/****************************************************************************
*                                                                           *
* Copyright (c) RSC. All rights reserved(2010).								*
* Developer: Marius Ionescu    						                        *
*                                                                           *
****************************************************************************/

using System;
using System.Collections;
using System.Data;
using System.Drawing;
using System.Web.Security;
using System.Web.UI.WebControls;
using NS_Controls;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.UI;
using AjaxControlToolkit;
using System.Web;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Threading;
using System.Globalization;
using System.IO;
using System.Text;

public partial class ExportDRG : SessionPageBase
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!IsPostBack)
            {
                LoadData();
            }
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
        }
    }

    protected void LoadData()
    {
        try
        {
            txtDataStart.Text = System.DateTime.Now.Date.ToString("dd/MM/yyyy");
            txtDataEnd.Text = System.DateTime.Now.Date.ToString("dd/MM/yyyy");

            DataSet ds_ExportType = Hipocrate.DB.DBConnection.GetData("select ID, Name from DTS_IMPEX where Active=1");
            DataView dv = ds_ExportType.Tables[0].DefaultView;

            DataSet ds_ExportStatus = Hipocrate.DB.DBConnection.GetData("select ID, Name from DRG_STATUSES where Active=1");
            DataView dvStatus = ds_ExportStatus.Tables[0].DefaultView;

            drpExportType.DataSource = dv;
            drpExportType.DataTextField = "name";
            drpExportType.DataValueField = "id";
            drpExportType.DataBind();

            drpTypeExport.DataSource = dv;
            drpTypeExport.DataTextField = "name";
            drpTypeExport.DataValueField = "id";
            drpTypeExport.DataBind();

            drpTypeExportVerify.DataSource = dv;
            drpTypeExportVerify.DataTextField = "name";
            drpTypeExportVerify.DataValueField = "id";
            drpTypeExportVerify.DataBind();

            drpExportStatus.DataSource = dvStatus;
            drpExportStatus.DataTextField = "name";
            drpExportStatus.DataValueField = "id";
            drpExportStatus.DataBind();
            drpExportStatus.Items.Insert(0, "Oricare");
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
        }
    }

    protected void LoadDataGrid()
    {
        OleDbConnection conn = null;
        try
        {
            string path = Server.MapPath("~/Tools/TempFiles/DRGNational2008.mdb");
            conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";");
            conn.Open();
            DataSet ds_Nomenclator = Hipocrate.DB.DBConnection.GetDataSet_Access("select * from " + drpNomenclator.SelectedItem.Text, conn);

            foreach (DataTable dt in ds_Nomenclator.Tables)
                foreach (DataColumn dc in dt.Columns)
                {
                    dc.ReadOnly = false;
                    dc.AllowDBNull = true;
                }

            SetSession("ds_Nomenclator", ds_Nomenclator);

            DataView dv = ds_Nomenclator.Tables[0].DefaultView;
            grDRG.SessionKey = GetSessionKey("ds_Nomenclator");
            grDRG.GridViewSortExpression = "";
            grDRG.EnableInsert = false;
            grDRG.SessionKeyIndex = 0;
            grDRG.SessionKeyFilter = dv.RowFilter;
            grDRG.SessionKeyColumnHeadersIndex = 1;
            grDRG.EnableEdit = false;
            grDRG.DataSource = dv;
            grDRG.DataBind();
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
        }
        finally
        {
            if (conn != null)
                conn.Close();
        }
    }

    protected void LoadDataGridVerify()
    {
        try
        {
            DataSet ds_NomVerify = null;
			object[] dates = new object[] { Tools.getDateFormat_CultureInfo(txtDataStart.Text), Tools.getDateFormat_CultureInfo(txtDataEnd.Text) };
			switch (drpTypeExportVerify.SelectedItem.Text)
            {
                case "Export DRG SPC":
                    ds_NomVerify = Hipocrate.DB.DBConnection.GetData("SelectExportData_DRG_SPC",
                        new string[] { "startDate", "endDate" }, dates);
                    break;

                case "Export DRG SPZ":
                    ds_NomVerify = Hipocrate.DB.DBConnection.GetData("SelectExportData_DRG_SPZ",
                        new string[] { "startDate", "endDate" }, dates);
                    break;
            }

            foreach (DataTable dt in ds_NomVerify.Tables)
                foreach (DataColumn dc in dt.Columns)
                {
                    dc.ReadOnly = false;
                    dc.AllowDBNull = true;
                }

            SetSession("ds_NomVerify", ds_NomVerify);

            DataView dv = ds_NomVerify.Tables[drpExportVerify.SelectedIndex].DefaultView;
            grExportVerify.SessionKey = GetSessionKey("ds_NomVerify");
            grExportVerify.GridViewSortExpression = "";
            grExportVerify.EnableInsert = false;
            grExportVerify.SessionKeyIndex = drpExportVerify.SelectedIndex;
            grExportVerify.SessionKeyFilter = dv.RowFilter;
            grExportVerify.SessionKeyColumnHeadersIndex = 1;
            grExportVerify.EnableEdit = false;
            grExportVerify.DataSource = dv;
            grExportVerify.DataBind();
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
        }
    }

    protected void LoadCombo(object sender, EventArgs e)
    {
        try
        {
            drpNomenclator.Items.Clear();
            ListItem lst_Medici = new ListItem("Lista_Medici", "1");
            ListItem lst_Sectii = new ListItem("Lista_Sectii___Filtrat", "2");
            ListItem lst_Spital = new ListItem("Lista_Spitale___Filtrat", "3");
            ListItem lst_Pacient_Fisa = new ListItem("Pacient_Fisa", "4");
            ListItem lst_Pacient_DiagnosticeSecundare = new ListItem("Pacient_DiagnosticeSecundare", "5");
            ListItem lst_Pacient_sectii = new ListItem("Pacient_sectii", "6");
            ListItem lst_Pacient_Proceduri = new ListItem("Pacient_Proceduri", "7");

            ListItem lst_Pacient_FisaSZ = new ListItem("Pacient_Fisa___SZ", "4");
            ListItem lst_Pacient_DiagnosticeSecundareSZ = new ListItem("Pacient_DiagnosticeSecundare___SZ", "5");
            ListItem lst_Pacient_ProceduriSZ = new ListItem("Pacient_Proceduri___SZ", "6");
            ListItem lst_Pacient_ServiciiSZ = new ListItem("Pacient_TipServicii___SZ", "7");
            ListItem lst_Pacient_AnalizeSZ = new ListItem("Pacient_AnalizeLaborator___SZ", "8");

            switch (drpTypeExport.SelectedItem.Text)
            {
                case "Export DRG SPC":
                    drpNomenclator.Items.Insert(0, lst_Medici);
                    drpNomenclator.Items.Insert(1, lst_Sectii);
                    drpNomenclator.Items.Insert(2, lst_Spital);
                    drpNomenclator.Items.Insert(3, lst_Pacient_Fisa);
                    drpNomenclator.Items.Insert(4, lst_Pacient_DiagnosticeSecundare);
                    drpNomenclator.Items.Insert(5, lst_Pacient_sectii);
                    drpNomenclator.Items.Insert(6, lst_Pacient_Proceduri);
                    break;

                case "Export DRG SPZ":
                    drpNomenclator.Items.Insert(0, lst_Medici);
                    drpNomenclator.Items.Insert(1, lst_Sectii);
                    drpNomenclator.Items.Insert(2, lst_Spital);
                    drpNomenclator.Items.Insert(3, lst_Pacient_FisaSZ);
                    drpNomenclator.Items.Insert(4, lst_Pacient_DiagnosticeSecundareSZ);
                    drpNomenclator.Items.Insert(5, lst_Pacient_ProceduriSZ);
                    drpNomenclator.Items.Insert(6, lst_Pacient_ServiciiSZ);
                    drpNomenclator.Items.Insert(7, lst_Pacient_AnalizeSZ);
                    break;
            }
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
        }
    }

    protected void LoadComboVerify(object sender, EventArgs e)
    {
        try
        {
            drpExportVerify.Items.Clear();
            ListItem lst_Medici = new ListItem("drg_Hospital", "1");
            ListItem lst_Sectii = new ListItem("vDRG_Medics", "2");
            ListItem lst_Spital = new ListItem("vDRG_Sections_List", "3");
            ListItem lst_Pacient_Fisa = new ListItem("vDRG_Pacient_Fisa_2008", "4");
            ListItem lst_Pacient_DiagnosticeSecundare = new ListItem("vDRG_Pacient_DiagnosticeSecundare_2008", "5");
            ListItem lst_Pacient_sectii = new ListItem("vDRG_Pacient_Sectii_2008", "6");
            ListItem lst_Pacient_Proceduri = new ListItem("vDRG_Procedures_2008", "7");

            ListItem lst_Pacient_FisaSZ = new ListItem("vDRG_Pacient_Fisa_Day", "4");
            ListItem lst_Pacient_DiagnosticeSecundareSZ = new ListItem("vDRG_Pacient_DiagnosticeSecundare_Day", "5");
            ListItem lst_Pacient_ProceduriSZ = new ListItem("vDRG_Procedures_Day", "6");
            ListItem lst_Pacient_ServiciiSZ = new ListItem("vDRG_Services_Day", "7");
            ListItem lst_Pacient_AnalizeSZ = new ListItem("vDRG_Analyses_Day", "8");


            switch (drpTypeExportVerify.SelectedItem.Text)
            {
                case "Export DRG SPC":
                    drpExportVerify.Items.Insert(0, lst_Medici);
                    drpExportVerify.Items.Insert(1, lst_Sectii);
                    drpExportVerify.Items.Insert(2, lst_Spital);
                    drpExportVerify.Items.Insert(3, lst_Pacient_Fisa);
                    drpExportVerify.Items.Insert(4, lst_Pacient_DiagnosticeSecundare);
                    drpExportVerify.Items.Insert(5, lst_Pacient_sectii);
                    drpExportVerify.Items.Insert(6, lst_Pacient_Proceduri);
                    break;

                case "Export DRG SPZ":
                    drpExportVerify.Items.Insert(0, lst_Medici);
                    drpExportVerify.Items.Insert(1, lst_Sectii);
                    drpExportVerify.Items.Insert(2, lst_Spital);
                    drpExportVerify.Items.Insert(3, lst_Pacient_FisaSZ);
                    drpExportVerify.Items.Insert(4, lst_Pacient_DiagnosticeSecundareSZ);
                    drpExportVerify.Items.Insert(5, lst_Pacient_ProceduriSZ);
                    drpExportVerify.Items.Insert(6, lst_Pacient_ServiciiSZ);
                    drpExportVerify.Items.Insert(7, lst_Pacient_AnalizeSZ);
                    break;
            }

            LoadDataGridVerify();
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
        }
    }

    protected void ddlNom_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadDataGrid();
    }

    protected void ddlNomVerify_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadDataGridVerify();
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        string[] tables = null;
        if (drpExportType.SelectedItem.Text == "Export DRG SPC")
            tables = new string[] { "Lista_Spitale (F)", "Lista_Medici", "Lista_Sectii (F)", "Pacient_Fisa", "Pacient_DiagnosticeSecundare", "Pacient_Sectii", "Pacient_Proceduri" };
        else
            tables = new string[] { "Lista_Spitale (F)", "Lista_Medici", "Lista_Sectii (F)", "Pacient_Fisa", "Pacient_DiagnosticeSecundare", "Pacient_Proceduri", "Pacient_Servicii", "Pacient_Analize" };

        output.Text = "";
        DateTime startAll = DateTime.Now;
        TimeSpan[] length = new TimeSpan[tables.Length];
        object[] size = new object[tables.Length];
        for (int i = 0; i < length.Length; ++i)
            length[i] = new TimeSpan(0, 0, 0);
        OleDbConnection conn = null;

		DateTime selectedStartDate = Tools.getDateFormat_CultureInfo(txtDataStart.Text);
		DateTime selectedEndDate = Tools.getDateFormat_CultureInfo(txtDataEnd.Text);
        object objStatus;
        if (drpExportStatus.SelectedIndex == 0)
            objStatus = System.DBNull.Value;
        else
            objStatus = drpExportStatus.SelectedValue;

        object[] paramInsert = new object[] { selectedStartDate, selectedEndDate, objStatus };
        object[] dates = new object[] { selectedStartDate, selectedEndDate };

        output.Text = objStatus.ToString();

		#region prepare to INSERT INTO ..DRG_EXPORTS
		string insertIntoDrgExports = @"
	INSERT INTO [{0}].[dbo].[DRG_EXPORTS]
		([DTSID]
		,[DRGStatusID]
		,[StaffID] , [UserName]
		,[RunSD] , [RunED]
		,[FileName]
		,[ErrCode]
		,[ExportSD] , [ExportED]
		--,[Validated] , [SectionCode]
		,[CntPacients] , [CntSecDiagnosis] , [CntProcedures]
		)
	VALUES
		({1}
		,NULL -- qv: select * from drg_statuses
		,{2} , '{3}'
		,'{4}' , '{5}'
		,NULL -- filename is 'DRGNational2008.mdb' but in another webapp
		,{8}
		,'{6}' , '{7}'
		--,NULL , NULL
		,{9} , {10} , {11}
		)";
		string dtsType = "0";
		DateTime startExport = DateTime.Now;
		string errorMessage = "NULL";
		object nPacienti = 0, nDiags = 0, nProceduri = 0;
		#endregion

		try
        {
            //select * from drg_Hospital					        - Lista_Spitale___Filtrat
            //select * from vDRG_Medics						        - Lista_Medici
            //select * from vDRG_Sections_List					    - Lista_Sectii___Filtrat
            //select * from vDRG_Pacient_Fisa_2008					- Pacient_Fisa
            //select * from vDRG_Pacient_DiagnosticeSecundare_2008	- Pacient_DiagnosticeSecundare
            //select * from vDRG_Pacient_Sectii_2008				- Pacient_sectii
            //select * from vDRG_Procedures_2008					- Pacient_Proceduri

            switch (drpExportType.SelectedItem.Text)
            {
                case "Export DRG SPC":
                    object spc = Hipocrate.DB.DBConnection.Execute("InsertPacient_Fisa_SPC",
                        new string[] { "SD", "ED", "iDRGStatusID" }, paramInsert);
					dtsType = "5"; // Export DRG_2008
					break;

                case "Export DRG SPZ":
                    object spz = Hipocrate.DB.DBConnection.Execute("InsertPacient_Fisa_SPZ",
                        new string[] { "SD", "ED", "iDRGStatusID" }, paramInsert);
					dtsType = "1"; // Export DRG Internari de zi
					break;
            }

            string path = Server.MapPath("~/Tools/TempFiles/DRGNational2008.mdb");
            conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";");
            conn.Open();
            object sqlReturnValue = null;

            int currentTable = 0;

            #region make sure SelectExportData_DRG exists...
            string sqlQuery = @"
IF not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SelectExportData_DRG]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
begin
	declare @apostrophe nvarchar(42)
	set @apostrophe = ''''
    
	declare @sp_creator nvarchar(4000)
	set @sp_creator = ' create procedure SelectExportData_DRG
			@startDate datetime,
			@endDate datetime
AS

DECLARE @DatabaseName nvarchar(250)
declare @a  nvarchar(2000)
declare @SD nvarchar(42)
declare @ED nvarchar(42)

set @DatabaseName = (select value from Global where code = ' + @apostrophe + 'SIUI_DatabaseName' + @apostrophe + ')
set @SD = convert(nvarchar(42), @startDate, 120)
set @ED = convert(nvarchar(42), @endDate, 120)

SET @a = ' + @apostrophe + 'Select * from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.drg_Hospital' + @apostrophe + '
exec sp_executesql @a

SET @a = ' + @apostrophe + 'Select * from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Medics' + @apostrophe + '
exec sp_executesql @a

SET @a = ' + @apostrophe + 'Select * from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Sections_List' + @apostrophe + '
exec sp_executesql @a

SET @a = ' + @apostrophe + 'Select * from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_Fisa_2008
	WHERE cazID in (select distinct Caz_ID from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_Sectii_2008
		WHERE DataExternare BETWEEN ' + @apostrophe + ' + char(39) + @SD + char(39) + ' + @apostrophe + ' AND ' + @apostrophe + ' + char(39) + @ED + char(39) + ' + @apostrophe + ')
	OR DataExternare BETWEEN ' + @apostrophe + ' + char(39) + @SD + char(39) + ' + @apostrophe + ' AND ' + @apostrophe + ' + char(39) + @ED + char(39)
exec sp_executesql @a

SET @a = ' + @apostrophe + 'Select * from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_DiagnosticeSecundare_2008
	WHERE caseID in (select distinct cazID from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_Fisa_2008
		WHERE DataExternare BETWEEN ' + @apostrophe + ' + char(39) + @SD + char(39) + ' + @apostrophe + ' AND ' + @apostrophe + ' + char(39) + @ED + char(39) + ' + @apostrophe + ')
	OR caseID in (select distinct Caz_ID from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_Sectii_2008
		WHERE DataExternare BETWEEN ' + @apostrophe + ' + char(39) + @SD + char(39) + ' + @apostrophe + ' AND ' + @apostrophe + ' + char(39) + @ED + char(39) + ' + @apostrophe + ')' + @apostrophe + '
exec sp_executesql @a

SET @a = ' + @apostrophe + 'Select * from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_Sectii_2008
	WHERE DataExternare BETWEEN ' + @apostrophe + ' + char(39) + @SD + char(39) + ' + @apostrophe + ' AND ' + @apostrophe + ' + char(39) + @ED + char(39)
exec sp_executesql @a

SET @a = ' + @apostrophe + 'Select * from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Procedures_2008
	where caz_ID in (select distinct cazID from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_Fisa_2008
		WHERE DataExternare BETWEEN ' + @apostrophe + ' + char(39) + @SD + char(39) + ' + @apostrophe + ' AND ' + @apostrophe + ' + char(39) + @ED + char(39) + ' + @apostrophe + ')
	OR caz_ID in (select distinct Caz_ID from ' + @apostrophe + ' + @DatabaseName + ' + @apostrophe + '.dbo.vDRG_Pacient_Sectii_2008
		WHERE DataExternare BETWEEN ' + @apostrophe + ' + char(39) + @SD + char(39) + ' + @apostrophe + ' AND ' + @apostrophe + ' + char(39) + @ED + char(39) + ' + @apostrophe + ')' + @apostrophe + '
exec sp_executesql @a
'

	--print @sp_creator
	exec  (@sp_creator)
end
";

            Hipocrate.DB.DBConnection.Execute(sqlQuery);
            #endregion

            DataSet dsSelectExportData_DRG = null;
			switch (drpExportType.SelectedItem.Text)
            {
                case "Export DRG SPC":
                    dsSelectExportData_DRG = Hipocrate.DB.DBConnection.GetData("SelectExportData_DRG_SPC",
						new string[] { "startDate", "endDate" }, dates);
                    break;

                case "Export DRG SPZ":
                    dsSelectExportData_DRG = Hipocrate.DB.DBConnection.GetData("SelectExportData_DRG_SPZ",
						new string[] { "startDate", "endDate" }, dates);
                    break;
            }

            //string sqlQueryPatients = @"select IdPacientFisa from Pacient_Fisa where DataExternare BETWEEN #" + Tools.getSQLFormat_120(Convert.ToDateTime(txtDataStart.Text)) + "# AND #" + Tools.getSQLFormat_120(Convert.ToDateTime(txtDataEnd.Text)) + "#";
            //DataSet ds_verifyPatients = Hipocrate.DB.DBConnection.GetDataSet_Access(sqlQueryPatients, conn);
            //string sqlDelete = null;
            //foreach (DataRow dr in ds_verifyPatients.Tables[0].Rows)
            //{
            //    sqlDelete = @"delete from Pacient_DiagnosticeSecundare where IdPacientFisa = " + dr["IdPacientFisa"].ToString();
            //    object deletePD = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(sqlDelete, path);
            //    sqlDelete = @"delete from Pacient_Proceduri where IdPacientFisa = " + dr["IdPacientFisa"].ToString();
            //    object deletePP = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(sqlDelete, path);
            //    sqlDelete = @"delete from Pacient_Sectii where IdPacientFisa = " + dr["IdPacientFisa"].ToString();
            //    object deletePS = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(sqlDelete, path);
            //    sqlDelete = @"delete from Pacient_Fisa where IdPacientFisa = " + dr["IdPacientFisa"].ToString();
            //    object deletePF = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(sqlDelete, path);
            //}

            #region spital
            DateTime start = DateTime.Now;
            DataView dv_Lista_Spitale___Filtrat = dsSelectExportData_DRG.Tables[0].DefaultView;

            //Session["ExportDRGStatus"] = "Inserare in Lista_Spitale___Filtrat";
            //string js_SpitaleFiltrat = "messageStatus(" + "Inserare in Lista_Spitale___Filtrat" + ")";
            //ScriptManager.RegisterStartupScript(this, typeof(ExportDRG), "messageStatus", js_SpitaleFiltrat, true);

            //string sqltopLSP = @"select top 1 * from Lista_Spitale___Filtrat";
            //DataSet ds_LSP = Hipocrate.DB.DBConnection.GetDataSet_Access(sqltopLSP, conn);

            if (dv_Lista_Spitale___Filtrat.Count > 0)
            {
                try
                {
                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Lista_Spitale___Filtrat (CodSpital, DenSpital, IdJudet, IsPublic, NumarPaturi,Implicit, CUI) VALUES('" + dv_Lista_Spitale___Filtrat[0]["DRG_Hospital_Code"].ToString().Trim() + "', '" + dv_Lista_Spitale___Filtrat[0]["DRG_Hospital_Name"].ToString().Trim() + "', " + dv_Lista_Spitale___Filtrat[0]["Hospital_CityID"].ToString().Trim() + ",true ," + dv_Lista_Spitale___Filtrat[0]["NoOfBeds"].ToString().Trim() + ", false, " + dv_Lista_Spitale___Filtrat[0]["CUI"].ToString().Trim() + ")", conn);
                }
                catch (ApplicationException ex)
                {
                    if (ex.Message.StartsWith("Operation must use an updateable query."))
                    {
						errorMessage = "'File `" + path + "` was Read-Only...'";
                        SetMessage("Va rugam setati fisierul `" + path + "` sa nu fie Read-Only!", true);
                        startAll = DateTime.MinValue;
                        return;
                    }
                    if (ex.Message.Contains("would create duplicate values in the index, primary key, or relationship"))
                    {
						//TODO: could continue (with UPDATE!) instead...
						errorMessage = "'File `" + path + "` was not emptied before the export...'";
                        SetMessage("Va rugam goliti fisierul inainte de a efectua exportul!", true);
                        startAll = DateTime.MinValue;
                        return;
                    }
                    throw;
                }
            }

            DataSet ds_IdSpital = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select IdSpital from Lista_Spitale___Filtrat where CodSpital = '" + dv_Lista_Spitale___Filtrat[0]["DRG_Hospital_Code"].ToString() + "'", conn);
            string idSpital = ds_IdSpital.Tables[0].Rows[0]["IdSpital"].ToString().Trim();
            length[currentTable] = DateTime.Now.Subtract(start);
            size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Lista_Spitale___Filtrat", conn).Tables[0].Rows[0][0];
            #endregion

            #region medici
            //string sqltopLM = @"select top 1 * from Lista_Medici";
            //DataSet ds_LM = Hipocrate.DB.DBConnection.GetDataSet_Access(sqltopLM, conn);
            //if (ds_LM.Tables[0].Rows.Count == 0)
            //{
            start = DateTime.Now;
            DataView dv_DRG_Medics = dsSelectExportData_DRG.Tables[1].DefaultView;

            for (int i = 0; i < dv_DRG_Medics.Count; ++i)
            {
                string codParafa = dv_DRG_Medics[i]["cod parafa"].ToString().Trim();
                if (codParafa.Length > 7)
                {
                    //TODO: show mesaj
                    codParafa = codParafa.Substring(0, 7);
                }
                sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Lista_Medici (DenMedic, CodParafa, IdSpecialitate) VALUES('" + dv_DRG_Medics[i]["Nume"].ToString().Trim() + "','" + codParafa + "', " + dv_DRG_Medics[i]["Specialitate"].ToString().Trim() + ")", conn);
            }
            //}

            Hashtable coduriMedic = new Hashtable();
            DataSet ds_Medics = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdMedic, CodParafa from Lista_Medici", conn);
            foreach (DataRow dr in ds_Medics.Tables[0].Rows)
                coduriMedic[dr["CodParafa"].ToString().Trim()] = dr["IdMedic"];
            coduriMedic["NULL"] = "NULL";
            coduriMedic[""] = "NULL";
            length[currentTable] = DateTime.Now.Subtract(start);
            size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Lista_Medici", conn).Tables[0].Rows[0][0];
            #endregion

            #region sectii_filtrat
            //string sqltopLSF = @"select top 1 * from Lista_Sectii___Filtrat";
            //DataSet ds_LSF = Hipocrate.DB.DBConnection.GetDataSet_Access(sqltopLSF, conn);
            //if (ds_LSF.Tables[0].Rows.Count == 0)
            //{
            start = DateTime.Now;
            DataView dv_DRG_Sections_List = dsSelectExportData_DRG.Tables[2].DefaultView;

            for (int i = 0; i < dv_DRG_Sections_List.Count; ++i)
            {
                sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Lista_Sectii___Filtrat (IdSpital, CodSectie, NumarSectie, IdTipFinantareSectie, Implicit, IdTipSectie) VALUES(" + idSpital + ",'" + dv_DRG_Sections_List[i]["code"].ToString().Trim() + "'," + dv_DRG_Sections_List[i]["SectionNo"].ToString().Trim() + ",NULL,False,1)", conn);
            }
            //}
            #endregion

            //***Spitalizare Continua***//
            int totalPacientiNepreluati = 0, totalPacientiNerepartizati = 0;
            Hashtable pacientiNepreluatiPeSectie = new Hashtable();
            List<string> pacientiNepreluati = new List<string>();
            List<string> pacientiNegasiti = new List<string>();
            int totalDiagnosticeNegasite = 0;
            Hashtable diagnosticeNegasite = new Hashtable();

            List<string> proceduriNepreluate = new List<string>();
            List<string> analizeNepreluate = new List<string>();
            List<string> serviciiNepreluate = new List<string>();

            if (drpExportType.SelectedItem.Text == "Export DRG SPC")
            {

                Hashtable sectii = new Hashtable();
                DataSet ds_Lista_Sectii___Filtrat = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdSectie, CodSectie, NumarSectie from Lista_Sectii___Filtrat", conn);
                foreach (DataRow dr in ds_Lista_Sectii___Filtrat.Tables[0].Rows)
                    sectii[new System.Collections.Generic.KeyValuePair<object, object>(dr["CodSectie"].ToString().Trim(), dr["NumarSectie"].ToString().Trim())] = dr["IdSectie"];
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Lista_Sectii___Filtrat", conn).Tables[0].Rows[0][0];

                #region codificare standard, deja existenta in mdb
                Hashtable coduriDiag = new Hashtable();
                DataSet ds_Diagnostice = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdDiagnostic, CodDiagnostic from Lista_Diagnostice", conn);
                foreach (DataRow dr in ds_Diagnostice.Tables[0].Rows)
                    coduriDiag[dr["CodDiagnostic"].ToString().Trim()] = dr["IdDiagnostic"];
                coduriDiag["NULL"] = "NULL";
                coduriDiag[""] = "NULL";

                Hashtable coduriProc = new Hashtable();
                DataSet ds_Proceduri = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdProcedura, CodProcedura from Lista_Proceduri", conn);
                foreach (DataRow dr in ds_Proceduri.Tables[0].Rows)
                    coduriProc[dr["CodProcedura"].ToString().Trim()] = dr["IdProcedura"];
                coduriProc["NULL"] = "NULL";
                coduriProc[""] = "NULL";

                //Hashtable parafaMedic = new Hashtable();
                //DataSet ds_MedicsParafa = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdMedic, CodParafa from Lista_Medici", conn);
                //foreach (DataRow dr in ds_MedicsParafa.Tables[0].Rows)
                //    parafaMedic[dr["CodParafa"].ToString().Trim()] = dr["CodParafa"];
                //parafaMedic["NULL"] = "NULL";
                //parafaMedic[""] = "NULL";
                #endregion

                #region pacienti
                start = DateTime.Now;
                DataView dv_Pacient_Fisa = dsSelectExportData_DRG.Tables[3].DefaultView;

                for (int i = 0; i < dv_Pacient_Fisa.Count; ++i)
                {
                    string cazId = dv_Pacient_Fisa[i]["CazID"].ToString().Trim();

                    string sectieCautata = dv_Pacient_Fisa[i]["Sectie_IDI"].ToString() + "-" + dv_Pacient_Fisa[i]["NumarSectieI"].ToString();
                    object sectieInt = sectii[new System.Collections.Generic.KeyValuePair<object, object>(dv_Pacient_Fisa[i]["Sectie_IDI"].ToString().Trim(), dv_Pacient_Fisa[i]["NumarSectieI"].ToString().Trim())];
                    if (sectieInt == null)
                    {
                        totalPacientiNepreluati++;
                        pacientiNepreluatiPeSectie[sectieCautata] = 1 + (int)(pacientiNepreluatiPeSectie.ContainsKey(sectieCautata) ? pacientiNepreluatiPeSectie[sectieCautata] : 0);
                        pacientiNepreluati.Add(cazId);
                        continue;
                    }
                    sectieCautata = dv_Pacient_Fisa[i]["Sectie_ID"].ToString() + "-" + dv_Pacient_Fisa[i]["NumarSectie"].ToString();
                    object sectieExt = sectii[new System.Collections.Generic.KeyValuePair<object, object>(dv_Pacient_Fisa[i]["Sectie_ID"].ToString().Trim(), dv_Pacient_Fisa[i]["NumarSectie"].ToString().Trim())];
                    if (sectieExt == null)
                    {
                        totalPacientiNepreluati++;
                        pacientiNepreluatiPeSectie[sectieCautata] = 1 + (int)(pacientiNepreluatiPeSectie.ContainsKey(sectieCautata) ? pacientiNepreluatiPeSectie[sectieCautata] : 0);
                        pacientiNepreluati.Add(cazId);
                        continue;
                    }

                    object codMedic = coduriMedic[dv_Pacient_Fisa[i]["CodMedic"].ToString().Trim()];
                    if (codMedic == null)
                    {
                        totalPacientiNepreluati++;
                        pacientiNepreluati.Add(cazId);
                        continue;
                    }

                    string idStareRecord = "";
                    if (Convert.ToInt32(dv_Pacient_Fisa[i]["IdStareRecord"]) == 0 || Convert.ToInt32(dv_Pacient_Fisa[i]["IdStareRecord"]) > 4)
                        idStareRecord = "1";
                    else
                        idStareRecord = Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim(), false).ToString();


                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Fisa 
				    (CazID,IdSpital,NFO,DataInternare,IdTipInternare,IdCriteriuInternare,IdSectieInternare,DataExternare,IdTipExternare,IdStareExternare,
				    IdSectieExternare,CNP,Nume,Prenume,IdLocalitate,Strada,NrStrada,DataNastere,Sex,GreutateInternare,
				    IdNivelInstruire,IdOcupatie,IdTipCetatenie,IdCetatenie,IdStatutAsigurat,IdTipAsigurareCNAS,IdCAS,IdCategorieAsiguratCNAS,IdMedicCurant,IdDI,
				    IdDP1,IdDP2,IdICP,IdMedicOperator,DataICP,IdAccident,IdStareValidare,IdStareRecord,IdTipFinantareSectie,SelectieExport,
				    Finalizat,IdDRG,CodValiditateDRG,CodValiditateICP,CodValiditateDP1,CodValiditateDP2,RegulaIncalcata,IdUtilizator,DataOperare,
				    SerieBilet, NumarBilet, CNPMama, NFOMama) VALUES('" + cazId + @"',"
                    + idSpital + @","
                    + dv_Pacient_Fisa[i]["NumarFoaieObservatie"].ToString().Trim() + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataInternare"]) + @","
                    + dv_Pacient_Fisa[i]["IdTipInternare"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCriteriuInternare"].ToString().Trim(), false) + @","
                        //+ dv_Pacient_Fisa[i]["Sectie_IDI"].ToString().Trim() + @","
                    + sectieInt + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataExternare"]) + @","
                    + dv_Pacient_Fisa[i]["IdTipExternare"].ToString().Trim() + @","
                    + dv_Pacient_Fisa[i]["IdStareExternare"].ToString().Trim() + @","
                        //+ dv_Pacient_Fisa[i]["Sectie_ID"].ToString().Trim() + @",'"
                    + sectieExt + @",'"
                    + dv_Pacient_Fisa[i]["CNP"].ToString().Trim() + @"',"
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Nume"].ToString().Trim(), true) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Prenume"].ToString().Trim(), true) + @","
                    + dv_Pacient_Fisa[i]["IdLocalitate"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Strada"].ToString().Trim(), true, 50) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["StradaNumar"].ToString().Trim(), true) + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataNastere"]) + @","
                    + GetGender(dv_Pacient_Fisa[i]["sex"].ToString()) + @","
                    + dv_Pacient_Fisa[i]["GreutateNastere"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdNivelInstruire"].ToString().Trim(), false) + @","
                        //+ dv_Pacient_Fisa[i]["IdNivelInstruire"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdOcupatie"].ToString().Trim(), false) + @","
                        //+ dv_Pacient_Fisa[i]["IdOcupatie"].ToString().Trim() + @","
                    + dv_Pacient_Fisa[i]["IdTipCetatenie"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCetatenie"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["IdStatutAsigurat"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDTipAsigurare"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDCAS"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCategorieAsiguratCNAS"].ToString().Trim(), false) + @","
                    + coduriMedic[dv_Pacient_Fisa[i]["CodMedic"].ToString().Trim()] + @","
                    + coduriDiag[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodDiagnosticLaInternare"].ToString().Trim(), false)] + @","
                    + coduriDiag[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod1"].ToString().Trim(), false)] + @","
                    + coduriDiag[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod2"].ToString().Trim(), false)] + @","
                    + coduriProc[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodInterventieChirurgicala"].ToString().Trim(), false)] + @","
                    + Tools.GetSQLValueNULL(coduriMedic[dv_Pacient_Fisa[i]["CodMedicOperator"].ToString().Trim()],false) + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataInterventieChirurgicalaPrincipala"]) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdAccident"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["StareValidare"].ToString().Trim(), false) + @","
                    + idStareRecord + @","
                        //+ Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim() == "0" ? "1" : dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdTipFinantareSectie"].ToString().Trim(), false) + @","
                    + ConvertBoolToYesNo(dv_Pacient_Fisa[i]["Flag_SelectieExport"]) + @","
                    + ConvertBoolToYesNo(dv_Pacient_Fisa[i]["Flag_Finalizat"]) + @","
                    + "NULL,"
                    + "NULL,"
                    + "Yes,"
                    + "Yes,"
                    + "Yes,"
                    + "NULL,"
                    + "1,"
                    + "NULL,"
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["SerieBilet"].ToString().Trim(), true, 10) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["NumarBilet"].ToString().Trim(), true, 10) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CNPMama"].ToString().Trim(), true) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["NFOMama"].ToString().Trim(), true) + ")", conn);
                }

                Hashtable coduriPacienti = new Hashtable();
                DataSet ds_PacientFisa = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdPacientFisa, CazID from Pacient_Fisa", conn);
                foreach (DataRow dr in ds_PacientFisa.Tables[0].Rows)
                    coduriPacienti[dr["CazID"].ToString().Trim()] = dr["IdPacientFisa"];
                coduriPacienti["NULL"] = "NULL";
                coduriPacienti[""] = "NULL";
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = nPacienti = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Fisa", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - diagnostice (secundare)
                start = DateTime.Now;
                DataView dv_Pacient_DiagnosticeSecundare = dsSelectExportData_DRG.Tables[4].DefaultView;

                for (int i = 0; i < dv_Pacient_DiagnosticeSecundare.Count; ++i)
                {
                    string cazId = dv_Pacient_DiagnosticeSecundare[i]["CaseID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }
                    string codDiag = dv_Pacient_DiagnosticeSecundare[i]["code"].ToString().Trim();
                    object idDiag = coduriDiag[Tools.GetSQLValueNULL(codDiag, false)];
                    if (idDiag == null)
                    {
                        ++totalDiagnosticeNegasite;
                        diagnosticeNegasite[codDiag] = 1 + (int)(diagnosticeNegasite.ContainsKey(codDiag) ? diagnosticeNegasite[codDiag] : 0);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_DiagnosticeSecundare(IdPacientFisa, IdDiagnosticSecundar) "
                         + " VALUES(" + idPacient + "," + idDiag + ")", conn);
                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = nDiags = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_DiagnosticeSecundare", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - sectii
                start = DateTime.Now;
                DataView dv_Pacient_Sectii = dsSelectExportData_DRG.Tables[5].DefaultView;

                for (int i = 0; i < dv_Pacient_Sectii.Count; ++i)
                {
                    string cazId = dv_Pacient_Sectii[i]["Caz_ID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string sectieCautata = dv_Pacient_Sectii[i]["Sectie_ID"].ToString() + "-" + dv_Pacient_Sectii[i]["NumarSectie"].ToString();
                    object sectieInt = sectii[new System.Collections.Generic.KeyValuePair<object, object>(dv_Pacient_Sectii[i]["Sectie_ID"].ToString().Trim(), dv_Pacient_Sectii[i]["NumarSectie"].ToString().Trim())];
                    if (sectieInt == null)
                    {
                        ++totalPacientiNerepartizati;
                        if (!pacientiNepreluatiPeSectie.ContainsKey(sectieCautata))
                            pacientiNepreluatiPeSectie[sectieCautata] = -1;
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Sectii (IdPacientFisa, IdSectie,DataInternare, DataExternare) VALUES("
                         + idPacient + ","
                         + sectieInt + ","
                         + Tools.getSQLFormat_ISO(dv_Pacient_Sectii[i]["DataInternare"]) + ","
                         + Tools.getSQLFormat_ISO(dv_Pacient_Sectii[i]["DataExternare"]) + ")", conn);

                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Sectii", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - proceduri
                start = DateTime.Now;
                DataView dv_Pacient_Proceduri = dsSelectExportData_DRG.Tables[6].DefaultView;

                for (int i = 0; i < dv_Pacient_Proceduri.Count; ++i)
                {
                    string cazId = dv_Pacient_Proceduri[i]["caz_ID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Proceduri(IdPacientFisa, IdProcedura, Cantitate, Invalid) VALUES("
                         + idPacient + ","
                         + coduriProc[dv_Pacient_Proceduri[i]["CodProcedura"].ToString().Trim()] + ","
                        //+ coduriProc[Tools.GetSQLValueNULL(dv_Pacient_Proceduri[i]["CodProcedura"].ToString().Trim(), false)] + ","
                         + Tools.GetSQLValueNULL(dv_Pacient_Proceduri[i]["Cantitate"].ToString().Trim(), false) + ", No)", conn);
                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = nProceduri = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Proceduri", conn).Tables[0].Rows[0][0];
                #endregion

                this.SetMessage(Resources.DatabaseStrings.DocumentSaved2 + " S-au importat " + (dv_Pacient_Fisa.Count - totalPacientiNepreluati) + " pacienti din " + dv_Pacient_Fisa.Count + ".", totalPacientiNepreluati > 0);
            }
            //***Spitalizare Zi***//
            else if (drpExportType.SelectedItem.Text == "Export DRG SPZ")
            {
                #region Pacient Fisa SZ
                Hashtable sectii = new Hashtable();
                List<string> patientsDuplicated = new List<string>();

                DataSet ds_Lista_Sectii___Filtrat = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdSectie, CodSectie, NumarSectie from Lista_Sectii___Filtrat", conn);
                foreach (DataRow dr in ds_Lista_Sectii___Filtrat.Tables[0].Rows)
                    sectii[new System.Collections.Generic.KeyValuePair<object, object>(dr["CodSectie"].ToString().Trim(), dr["NumarSectie"].ToString().Trim())] = dr["IdSectie"];
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Lista_Sectii___Filtrat", conn).Tables[0].Rows[0][0];

                #region codificare standard, deja existenta in mdb
                Hashtable coduriDiag = new Hashtable();
                DataSet ds_Diagnostice = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdDiagnostic, CodDiagnostic from Lista_Diagnostice", conn);
                foreach (DataRow dr in ds_Diagnostice.Tables[0].Rows)
                    coduriDiag[dr["CodDiagnostic"].ToString().Trim()] = dr["IdDiagnostic"];
                coduriDiag["NULL"] = "NULL";
                coduriDiag[""] = "NULL";

                Hashtable coduriProc = new Hashtable();
                DataSet ds_Proceduri = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdProcedura, CodProcedura from Lista_Proceduri", conn);
                foreach (DataRow dr in ds_Proceduri.Tables[0].Rows)
                    coduriProc[dr["CodProcedura"].ToString().Trim()] = dr["IdProcedura"];
                coduriProc["NULL"] = "NULL";
                coduriProc[""] = "NULL";

                Hashtable codesServices = new Hashtable();
                DataSet ds_Services = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdTipServiciu, DenTipServiciu from Lista_TipServicii", conn);
                foreach (DataRow dr in ds_Services.Tables[0].Rows)
                    codesServices[dr["DenTipServiciu"].ToString().Trim()] = dr["IdTipServiciu"];
                codesServices["NULL"] = "NULL";
                codesServices[""] = "NULL";

                Hashtable codesAnalises = new Hashtable();
                DataSet ds_Analises = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdAnaliza, CodAnaliza from Lista_Analize", conn);
                foreach (DataRow dr in ds_Analises.Tables[0].Rows)
                    codesAnalises[dr["CodAnaliza"].ToString().Trim()] = dr["IdAnaliza"];
                codesAnalises["NULL"] = "NULL";
                codesAnalises[""] = "NULL";

                #endregion
                start = DateTime.Now;
                DataView dv_Pacient_Fisa = dsSelectExportData_DRG.Tables[3].DefaultView;

                for (int i = 0; i < dv_Pacient_Fisa.Count; ++i)
                {
                    string cazId = dv_Pacient_Fisa[i]["Caz_ID"].ToString().Trim();
                    patientsDuplicated.Add(cazId);

                    if (!patientsDuplicated.Contains(cazId))
                    {
                        //pacientiNepreluati.Add(cazId);
                        //continue;
                    }

                    string sectieCautata = dv_Pacient_Fisa[i]["Sectie_ID"].ToString() + "-" + dv_Pacient_Fisa[i]["NumarSectie"].ToString();
                    object sectieInt = sectii[new System.Collections.Generic.KeyValuePair<object, object>(dv_Pacient_Fisa[i]["Sectie_ID"].ToString().Trim(), dv_Pacient_Fisa[i]["NumarSectie"].ToString().Trim())];
                    if (sectieInt == null)
                    {
                        totalPacientiNepreluati++;
                        pacientiNepreluatiPeSectie[sectieCautata] = 1 + (int)(pacientiNepreluatiPeSectie.ContainsKey(sectieCautata) ? pacientiNepreluatiPeSectie[sectieCautata] : 0);
                        pacientiNepreluati.Add(cazId);
                        continue;
                    }

                    if (dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod1"] == System.DBNull.Value)
                    {
                        totalPacientiNepreluati++;
                        pacientiNepreluati.Add(cazId);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Fisa___SZ
				    (CazID,IdSpital,IdSectie,NFO,DataDeschidereFisa,DataInchidereFisa,CNP,Nume,Prenume,IdLocalitate,Strada,NrStrada,DataNastere,Sex,
				    IdNivelInstruire,IdOcupatie,IdTipCetatenie,IdCetatenie,IdStatutAsigurat,IdTipAsigurareCNAS,IdCAS,IdCategorieAsiguratCNAS,IdMedic,
				    IdDP1,IdDP2,IdAccident,IdStareRecord,IdTipFinantare,SelectieExport,
				    Finalizat,IdUtilizator,DataOperare) VALUES('" + cazId + @"',"
                    + idSpital + @","
                    + sectieInt + @","
                    + dv_Pacient_Fisa[i]["NumarFoaieObservatie"].ToString().Trim() + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataDeschidereFisa"]) + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataInchidereFisa"]) + @","
                     + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CNP"].ToString().Trim(), true) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Nume"].ToString().Trim(), true) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Prenume"].ToString().Trim(), true) + @","
                    + dv_Pacient_Fisa[i]["IdLocalitate"].ToString().Trim() + @","
                    + "NULL,"
                    + "NULL,"
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataNastere"]) + @","
                    + GetGender(dv_Pacient_Fisa[i]["sex"].ToString()) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdNivelInstruire"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdOcupatie"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["IdTipCetatenie"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCetatenie"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["IdStatutAsigurat"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDTipAsigurareCNAS"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDCAS"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCategorieAsiguratCNAS"].ToString().Trim(), false) + @","
                    + coduriMedic[dv_Pacient_Fisa[i]["CodMedic"].ToString().Trim()] + @","
                    + coduriDiag[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod1"].ToString().Trim(), false)] + @","
                    + coduriDiag[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod2"].ToString().Trim(), false)] + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdAccident"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim() == "0" ? "1" : dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdTipFinantare"].ToString().Trim(), false) + @","
                    + ConvertBoolToYesNo(dv_Pacient_Fisa[i]["Flag_SelectieExport"]) + @","
                    + ConvertBoolToYesNo(dv_Pacient_Fisa[i]["Flag_Finalizat"]) + @","
                    + "1,"
                    + "NULL)", conn);
                }

                Hashtable coduriPacienti = new Hashtable();
                DataSet ds_PacientFisa = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdPacientFisa, CazID from Pacient_Fisa___SZ", conn);
                foreach (DataRow dr in ds_PacientFisa.Tables[0].Rows)
                    coduriPacienti[dr["CazID"].ToString().Trim()] = dr["IdPacientFisa"];
                coduriPacienti["NULL"] = "NULL";
                coduriPacienti[""] = "NULL";
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = nPacienti = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Fisa___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - diagnostice (secundare) SZ
                start = DateTime.Now;
                DataView dv_Pacient_DiagnosticeSecundare = dsSelectExportData_DRG.Tables[4].DefaultView;

                for (int i = 0; i < dv_Pacient_DiagnosticeSecundare.Count; ++i)
                {
                    string cazId = dv_Pacient_DiagnosticeSecundare[i]["CaseID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string codDiag = dv_Pacient_DiagnosticeSecundare[i]["code"].ToString().Trim();
                    object idDiag = coduriDiag[Tools.GetSQLValueNULL(codDiag, false)];
                    if (idDiag == null)
                    {
                        ++totalDiagnosticeNegasite;
                        diagnosticeNegasite[codDiag] = 1 + (int)(diagnosticeNegasite.ContainsKey(codDiag) ? diagnosticeNegasite[codDiag] : 0);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_DiagnosticeSecundare___SZ(IdPacientFisa, IdDiagnosticSecundar) "
                         + " VALUES(" + idPacient + "," + idDiag + ")", conn);
                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = nDiags = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_DiagnosticeSecundare___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - proceduri
                start = DateTime.Now;
                DataView dv_Pacient_Procedures = dsSelectExportData_DRG.Tables[5].DefaultView;
                DataTable pacientVisit_Table = dv_Pacient_Procedures.ToTable("Pacient_Visit", true, "caz_id", "ProcDate");

                for (int i = 0; i < pacientVisit_Table.Rows.Count; ++i)
                {
                    string cazId = pacientVisit_Table.Rows[i]["caz_ID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Vizite___SZ(IdPacientFisa, DataVizita) "
                         + " VALUES(" + idPacient + ","
                         + Tools.getSQLFormat_ISO(pacientVisit_Table.Rows[i]["ProcDate"]) + ")", conn);
                }

                for (int i = 0; i < dv_Pacient_Procedures.Count; ++i)
                {
                    string strQueryAccess = "select [Pacient_Vizite___SZ].IdPacientVizita from Pacient_Fisa___SZ INNER JOIN [Pacient_Vizite___SZ] ON [Pacient_Vizite___SZ].IdPacientFisa = Pacient_Fisa___SZ.IdPacientFisa where Pacient_Fisa___SZ.CazId = '" + dv_Pacient_Procedures[i]["caz_id"].ToString().Trim() + "' AND [Pacient_Vizite___SZ].DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Procedures[i]["ProcDate"])) + "#";
                    DataSet dsidPacientVizita = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);
                    object idPacientVizita = null;

                    if (dsidPacientVizita.Tables[0].Rows.Count > 0)
                        idPacientVizita = dsidPacientVizita.Tables[0].Rows[0][0];
                    else if (idPacientVizita == null)
                    {
                        proceduriNepreluate.Add(dv_Pacient_Procedures[i]["caz_id"].ToString());
                        continue;
                    }

                    string codProc = dv_Pacient_Procedures[i]["codprocedura"].ToString().Trim();
                    object idProc = coduriProc[Tools.GetSQLValueNULL(codProc, false)];
                    if (idProc == null)
                    {
                        proceduriNepreluate.Add(dv_Pacient_Procedures[i]["caz_id"].ToString());
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Proceduri___SZ(IdPacientVizita, IdProcedura,Cantitate) "
                         + " VALUES(" + idPacientVizita + ","
                         + coduriProc[Tools.GetSQLValueNULL(dv_Pacient_Procedures[i]["CodProcedura"].ToString().Trim(), false)] + @","
                         + Tools.GetSQLValueNULL(dv_Pacient_Procedures[i]["Cantitate"].ToString().Trim(), false) + ")", conn);
                }

                length[currentTable] = DateTime.Now.Subtract(start);
				size[currentTable++] = nProceduri = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Proceduri___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - servicii
                start = DateTime.Now;
                DataView dv_Pacient_Services = dsSelectExportData_DRG.Tables[6].DefaultView;
                DataTable pacientVisit_TableService = dv_Pacient_Services.ToTable("Pacient_Visit", true, "caseid", "ServiceDate");

                for (int i = 0; i < pacientVisit_TableService.Rows.Count; ++i)
                {
                    string cazId = pacientVisit_TableService.Rows[i]["caseID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string strQueryAccess = "select IdPacientFisa, DataVizita from Pacient_Vizite___SZ where IdPacientFisa=" + idPacient + " AND DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Services[i]["ServiceDate"])) + "#";
                    DataSet dsverifyData = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);

                    if (dsverifyData.Tables[0].Rows.Count == 0)
                    {
                        sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Vizite___SZ(IdPacientFisa, DataVizita) "
                             + " VALUES(" + idPacient + ","
                             + Tools.getSQLFormat_ISO(pacientVisit_TableService.Rows[i]["ServiceDate"]) + ")", conn);
                    }
                    else
                        continue;
                }

                for (int i = 0; i < dv_Pacient_Services.Count; ++i)
                {
                    string strQueryAccess = "select [Pacient_Vizite___SZ].IdPacientVizita from Pacient_Fisa___SZ INNER JOIN [Pacient_Vizite___SZ] ON [Pacient_Vizite___SZ].IdPacientFisa = Pacient_Fisa___SZ.IdPacientFisa where Pacient_Fisa___SZ.CazId = '" + dv_Pacient_Services[i]["caseID"].ToString().Trim() + "' AND [Pacient_Vizite___SZ].DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Services[i]["ServiceDate"])) + "#";
                    DataSet dsidPacientVizita = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);
                    object idPacientVizita = null;
                    if (dsidPacientVizita.Tables[0].Rows.Count > 0)
                        idPacientVizita = dsidPacientVizita.Tables[0].Rows[0][0];
                    else if (idPacientVizita == null)
                        continue;

                    if (dv_Pacient_Services[i]["code"] == System.DBNull.Value)
                    {
                        serviciiNepreluate.Add(dv_Pacient_Services[i]["caseid"].ToString());
                        continue;
                    }

                    object idService = codesServices[Tools.GetSQLValueNULL(dv_Pacient_Services[i]["Code"].ToString().Trim(), false)];
                    if (idService == null)
                    {
                        serviciiNepreluate.Add(dv_Pacient_Services[i]["caseid"].ToString());
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_TipServicii___SZ(IdPacientVizita, IdTipServiciu) "
                         + " VALUES(" + idPacientVizita + ","
                         + codesServices[Tools.GetSQLValueNULL(dv_Pacient_Services[i]["Code"].ToString().Trim(), false)] + ")", conn);
                }

                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_TipServicii___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - analize
                start = DateTime.Now;
                DataView dv_Pacient_Analises = dsSelectExportData_DRG.Tables[7].DefaultView;
                DataTable pacientVisit_TableAnalise = dv_Pacient_Analises.ToTable("Pacient_Visit", true, "caz_id", "AnalyseDate");

                for (int i = 0; i < pacientVisit_TableAnalise.Rows.Count; ++i)
                {
                    string cazId = pacientVisit_TableAnalise.Rows[i]["caz_id"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string strQueryAccess = "select IdPacientFisa, DataVizita from Pacient_Vizite___SZ where IdPacientFisa=" + idPacient + " AND DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(pacientVisit_TableAnalise.Rows[i]["AnalyseDate"])) + "#";
                    DataSet dsverifyData = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);

                    if (dsverifyData.Tables[0].Rows.Count == 0)
                    {
                        sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Vizite___SZ(IdPacientFisa, DataVizita) "
                        + " VALUES(" + idPacient + ","
                        + Tools.getSQLFormat_ISO(pacientVisit_TableAnalise.Rows[i]["AnalyseDate"]) + ")", conn);
                    }
                    else
                        continue;
                }

                for (int i = 0; i < dv_Pacient_Analises.Count; ++i)
                {
                    string strQueryAccess = "select [Pacient_Vizite___SZ].IdPacientVizita from Pacient_Fisa___SZ INNER JOIN [Pacient_Vizite___SZ] ON [Pacient_Vizite___SZ].IdPacientFisa = Pacient_Fisa___SZ.IdPacientFisa where Pacient_Fisa___SZ.CazId = '" + dv_Pacient_Analises[i]["caz_id"].ToString().Trim() + "' AND [Pacient_Vizite___SZ].DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Analises[i]["AnalyseDate"])) + "#";
                    DataSet dsidPacientVizita = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);
                    object idPacientVizita = null;
                    if (dsidPacientVizita.Tables[0].Rows.Count > 0)
                        idPacientVizita = dsidPacientVizita.Tables[0].Rows[0][0];
                    else if (idPacientVizita == null)
                        continue;

                    if (dv_Pacient_Analises.Table.Rows[i]["CodAnaliza"] == System.DBNull.Value )
                    {
                        analizeNepreluate.Add(dv_Pacient_Analises[i]["caz_id"].ToString());
                        continue;
                    }

                    object idAnaliza = codesAnalises[Tools.GetSQLValueNULL(dv_Pacient_Analises[i]["CodAnaliza"].ToString().Trim(), false)];
                    if (idAnaliza == null)
                    {
                        analizeNepreluate.Add(dv_Pacient_Analises[i]["caz_id"].ToString());
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_AnalizeLaborator___SZ(IdPacientVizita, IdAnaliza,Cantitate) "
                         + " VALUES(" + idPacientVizita + ","
                         + codesAnalises[Tools.GetSQLValueNULL(dv_Pacient_Analises[i]["CodAnaliza"].ToString().Trim(), false)] + @","
                         + Tools.GetSQLValueNULL(dv_Pacient_Analises[i]["Cantitate"].ToString().Trim(), false) + ")", conn);
                }

                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_AnalizeLaborator___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                this.SetMessage(Resources.DatabaseStrings.DocumentSaved2 + " S-au importat " + (dv_Pacient_Fisa.Count - totalPacientiNepreluati) + " pacienti din " + dv_Pacient_Fisa.Count + ".", totalPacientiNepreluati > 0);
            }

            #region detalii erori
            output.Text += "Export terminat. <font color=\"red\">&nbsp;";
            if (totalPacientiNepreluati > 0)
            {
                output.Text += "<br/><br/>Numar pacienti nepreluati: " + totalPacientiNepreluati;
                foreach (string sectieNegasita in pacientiNepreluatiPeSectie.Keys)
                    if (((int)pacientiNepreluatiPeSectie[sectieNegasita]) > 0)
                        output.Text += string.Format("<br/>Sectia '{0}' : {1}", sectieNegasita, pacientiNepreluatiPeSectie[sectieNegasita]);
            }
            if (proceduriNepreluate.Count > 0)
            {
                output.Text += "<br/><br/>Proceduri care nu au corespondent cu pacientii: " + proceduriNepreluate.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in proceduriNepreluate)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (serviciiNepreluate.Count > 0)
            {
                output.Text += "<br/><br/>Servicii care nu au fost preluate pe pacientii: " + serviciiNepreluate.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in serviciiNepreluate)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (analizeNepreluate.Count > 0)
            {
                output.Text += "<br/><br/>Analize care nu au fost preluate pe pacientii: " + analizeNepreluate.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in analizeNepreluate)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (pacientiNegasiti.Count > 0)
            {
                output.Text += "<br/><br/>Pacienti care nu au fost gasiti : " + pacientiNegasiti.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in pacientiNegasiti)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (totalPacientiNerepartizati > 0)
            {
                output.Text += "<br/><br/>Pacienti nerepartizati pe sectie (in Pacient_Sectii), din cauza sectiei gresite: " + totalPacientiNerepartizati;
                foreach (string sectieGresita in pacientiNepreluatiPeSectie.Keys)
                    if (((int)pacientiNepreluatiPeSectie[sectieGresita]) < 0)
                        output.Text += string.Format("<br/>Sectia '{0}' : {1}", sectieGresita, pacientiNepreluatiPeSectie[sectieGresita]);
            }
            if (totalDiagnosticeNegasite > 0)
            {
                output.Text += "<br/><br/>Diagnostice care nu au fost gasite in tabela standard : " + totalDiagnosticeNegasite;
                foreach (string codDiag in diagnosticeNegasite.Keys)
                    output.Text += string.Format("<br/>Cod Diagnostic '{0}' : {1}", codDiag, diagnosticeNegasite[codDiag]);
            }
            output.Text += "&nbsp;</font>";
            #endregion
        }
        catch (Exception ex)
        {
			errorMessage = ex.Message.Replace("'", "`"); //NB: don't use "''" since there's a 1% chance of substring'ing right between those two (!)
			errorMessage = "'" + (errorMessage.Length >= 350 ? errorMessage.Substring(0, 349) : errorMessage) + "'";
            //this.SetMessage(ex.Message, true);
            if (output.Text.Length > 0)
                output.Text += "<br /><br />";
            output.Text += "<strong>Exceptie</strong>: " + ex.ToString();
        }
        finally
        {
			try
			{
				object dbName = Hipocrate.DB.DBConnection.GetData("select value from Global where code = 'SIUI_DatabaseName'").Tables[0].Rows[0][0];
				string dateFormat = "yyyy-MM-dd HH:mm:ss";
				Hipocrate.DB.DBConnection.Execute(string.Format(insertIntoDrgExports, dbName, dtsType,
					Tools.StaffID, Tools.userLogin, selectedStartDate.ToString(dateFormat), selectedEndDate.ToString(dateFormat),
					startExport.ToString(dateFormat), DateTime.Now.ToString(dateFormat), errorMessage,
					nPacienti, nDiags, nProceduri));
			}
			catch (Exception ex)
			{
				if (output.Text.Length > 0)
					output.Text += "<br /><br />";
				output.Text += "<strong>Exceptie salvare info</strong>: " + ex.ToString();
			}
			if (conn != null)
                conn.Close();
            string js = "hideModal()";
            ScriptManager.RegisterStartupScript(this, typeof(ExportDRG), "hideModal", js, true);
            if (startAll != DateTime.MinValue)
            {
                output.Text += "<br/><br/><table><tr><th>Durata de executie:</th><th>" + TimeSpanToString(DateTime.Now.Subtract(startAll)) + "</th><th>&nbsp;</th></tr>";
                for (int i = 0; i < tables.Length; ++i)
                    output.Text += string.Format("<tr><td>{0}</td><td>{1}</td><td align=\"right\">{2}</td></tr>", tables[i], TimeSpanToString(length[i]), size[i]);
                output.Text += "</table>";
                tblTitlu.Style.Add("width", "99.3%");
            }
        }
    }

    protected void btnSaveDRG_Template_Click(object sender, EventArgs e)
    {
        string[] tables = null;
        if (drpExportType.SelectedItem.Text == "Export DRG SPC")
            tables = new string[] { "Lista_Spitale (F)", "Pacient_Fisa", "Pacient_DiagnosticeSecundare", "Pacient_Sectii", "Pacient_Proceduri" };
        else
            tables = new string[] { "Lista_Spitale (F)", "Pacient_Fisa", "Pacient_DiagnosticeSecundare", "Pacient_Proceduri", "Pacient_Servicii", "Pacient_Analize" };

        output.Text = "";
        DateTime startAll = DateTime.Now;
        TimeSpan[] length = new TimeSpan[tables.Length];
        object[] size = new object[tables.Length];
        for (int i = 0; i < length.Length; ++i)
            length[i] = new TimeSpan(0, 0, 0);
        OleDbConnection conn = null;
        try
        {
            string path = "";
            object objStatus;
            if (drpExportStatus.SelectedIndex == 0)
                objStatus = System.DBNull.Value;
            else
                objStatus = drpExportStatus.SelectedValue;

			object[] dates = new object[] { Tools.getDateFormat_CultureInfo(txtDataStart.Text), Tools.getDateFormat_CultureInfo(txtDataEnd.Text) };
            object[] paramInsert = new object[] { Tools.getDateFormat_CultureInfo(txtDataStart.Text), Tools.getDateFormat_CultureInfo(txtDataEnd.Text), objStatus };
			switch (drpExportType.SelectedItem.Text)
            {
                case "Export DRG SPC":
                    object spc = Hipocrate.DB.DBConnection.Execute("InsertPacient_Fisa_SPC",
                        new string[] { "SD", "ED" }, paramInsert);
                    path = Server.MapPath("~/Tools/TempFiles/FisierExportSMDP_Template.mdb");
                    break;

                case "Export DRG SPZ":
                    object spz = Hipocrate.DB.DBConnection.Execute("InsertPacient_Fisa_SPZ",
                       new string[] { "SD", "ED" }, paramInsert);
                    path = Server.MapPath("~/Tools/TempFiles/FisierExportSMDPZ_Template.mdb");
                    break;
            }

            conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";");
            conn.Open();
            object sqlReturnValue = null;

            int currentTable = 0;

            DataSet dsSelectExportData_DRG = null;
            switch (drpExportType.SelectedItem.Text)
            {
                case "Export DRG SPC":
                    dsSelectExportData_DRG = Hipocrate.DB.DBConnection.GetData("SelectExportData_DRG_SPC",
                        new string[] { "startDate", "endDate" }, dates);
                    break;

                case "Export DRG SPZ":
                    dsSelectExportData_DRG = Hipocrate.DB.DBConnection.GetData("SelectExportData_DRG_SPZ",
                        new string[] { "startDate", "endDate" }, dates);
                    break;
            }

            #region spital
            DateTime start = DateTime.Now;
            DataView dv_Lista_Spitale___Filtrat = dsSelectExportData_DRG.Tables[0].DefaultView;

            if (dv_Lista_Spitale___Filtrat.Count > 0)
            {
                try
                {
                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Lista_Spitale___Filtrat (CodSpital, IsPublic, CUI) VALUES('" + dv_Lista_Spitale___Filtrat[0]["DRG_Hospital_Code"].ToString().Trim() + "', true ," + dv_Lista_Spitale___Filtrat[0]["CUI"].ToString().Trim() + ")", conn);
                }
                catch (ApplicationException ex)
                {
                    if (ex.Message.StartsWith("Operation must use an updateable query."))
                    {
                        SetMessage("Va rugam setati fisierul `" + path + "` sa nu fie Read-Only!", true);
                        startAll = DateTime.MinValue;
                        return;
                    }
                    if (ex.Message.Contains("would create duplicate values in the index, primary key, or relationship"))
                    {
                        SetMessage("Va rugam goliti fisierul inainte de a efectua exportul!", true);
                        startAll = DateTime.MinValue;
                        return;
                    }
                    throw;
                }
            }

            DataSet ds_IdSpital = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select IdSpital from Lista_Spitale___Filtrat where CodSpital = '" + dv_Lista_Spitale___Filtrat[0]["DRG_Hospital_Code"].ToString() + "'", conn);
            string idSpital = ds_IdSpital.Tables[0].Rows[0]["IdSpital"].ToString().Trim();
            length[currentTable] = DateTime.Now.Subtract(start);
            size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Lista_Spitale___Filtrat", conn).Tables[0].Rows[0][0];
            #endregion

            //***Spitalizare Continua***//
            int totalPacientiNepreluati = 0, totalPacientiNerepartizati = 0;
            int totalDiagnosticeNegasite = 0;
            Hashtable pacientiNepreluatiPeSectie = new Hashtable();
            List<string> pacientiNepreluati = new List<string>();
            List<string> pacientiNegasiti = new List<string>();
            
            Hashtable diagnosticeNegasite = new Hashtable();
            List<string> proceduriNepreluate = new List<string>();
            List<string> analizeNepreluate = new List<string>();
            List<string> serviciiNepreluate = new List<string>();

            List<string> patientFO = new List<string>();
            List<string> patientDataInternare = new List<string>();
            List<string> patientIdInternare = new List<string>();

            if (drpExportType.SelectedItem.Text == "Export DRG SPC")
            {
                #region pacienti
                start = DateTime.Now;
                DataView dv_Pacient_Fisa = dsSelectExportData_DRG.Tables[3].DefaultView;

                for (int i = 0; i < dv_Pacient_Fisa.Count; ++i)
                {
                    string cazId = dv_Pacient_Fisa[i]["CazID"].ToString().Trim();
                    if (string.IsNullOrEmpty(cazId) || string.IsNullOrEmpty(idSpital))
                        continue;

                    if (dv_Pacient_Fisa[i]["NumarFoaieObservatie"] == System.DBNull.Value)
                    {
                        if (!patientFO.Contains(cazId))
                                patientFO.Add(cazId);
                        continue;
                    }

                    if (dv_Pacient_Fisa[i]["DataInternare"] == System.DBNull.Value)
                    {
                        if (!patientDataInternare.Contains(cazId))
                            patientDataInternare.Add(cazId);
                        continue;
                    }

                    if (dv_Pacient_Fisa[i]["IdTipInternare"] == System.DBNull.Value)
                    {
                        if (!patientIdInternare.Contains(cazId))
                            patientIdInternare.Add(cazId);
                        continue;
                    }

                    string idStareRecord = "";
                    if (Convert.ToInt32(dv_Pacient_Fisa[i]["IdStareRecord"]) == 0 || Convert.ToInt32(dv_Pacient_Fisa[i]["IdStareRecord"]) > 4)
                        idStareRecord = "1";
                    else
                        idStareRecord = Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim(), false).ToString();

                    if (dv_Pacient_Fisa[i]["IdStareRecord"] == System.DBNull.Value)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Fisa 
				    (CazID,CodSpital,NFO,AnInternare,DataInternare,IdTipInternare,IdCriteriuInternare,CodSectieInternare,NrSectieInternare,DataExternare,
				    IdTipExternare,IdStareExternare,CodSectieExternare,NrSectieExternare,CNP,IdLocalitate,Strada,NrStrada,DataNastere,Sex,GreutateInternare,
				    IdNivelInstruire,IdOcupatie,IdTipCetatenie,IdCetatenie,IdStatutAsigurat,IdTipAsigurareCNAS,IdCAS,IdCategorieAsiguratCNAS,CodMedicCurant,IdSpecialitate,
				    CodDI,CodDP1,CodDP2,CodICP,CodMedicOperator,DataICP,IdStareRecord,IdAccident,IdTipFinantareSectie,CNPMama, NFOMama) VALUES('" 
                    + cazId + @"',"
                    + idSpital + @","
                    + dv_Pacient_Fisa[i]["NumarFoaieObservatie"].ToString().Trim() + @","
                    + dv_Pacient_Fisa[i]["AnInternare"].ToString().Trim() + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataInternare"]) + @","
                    + dv_Pacient_Fisa[i]["IdTipInternare"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCriteriuInternare"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["Sectie_IDI"].ToString().Trim() + @","
                    + dv_Pacient_Fisa[i]["NumarSectieI"].ToString().Trim() + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataExternare"]) + @","
                    + dv_Pacient_Fisa[i]["IdTipExternare"].ToString().Trim() + @","
                    + dv_Pacient_Fisa[i]["IdStareExternare"].ToString().Trim() + @","
                    + dv_Pacient_Fisa[i]["Sectie_ID"].ToString().Trim() + @","
                    + dv_Pacient_Fisa[i]["NrSectieExternare"].ToString().Trim() + @",'"
                    + dv_Pacient_Fisa[i]["CNP"].ToString().Trim() + @"',"
                    + dv_Pacient_Fisa[i]["IdLocalitate"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Strada"].ToString().Trim(), true, 50) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["StradaNumar"].ToString().Trim(), true) + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataNastere"]) + @","
                    + GetGender(dv_Pacient_Fisa[i]["sex"].ToString()) + @","
                    + dv_Pacient_Fisa[i]["GreutateNastere"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdNivelInstruire"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdOcupatie"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["IdTipCetatenie"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCetatenie"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["IdStatutAsigurat"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDTipAsigurare"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDCAS"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCategorieAsiguratCNAS"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["CodMedic"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdMedicSpec"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["CodDiagnosticLaInternare"] + @","
                    + dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod1"] + @","
                    + dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod2"] + @","
                    + dv_Pacient_Fisa[i]["CodInterventieChirurgicala"] + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodMedicOperator"].ToString(),false) + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataInterventieChirurgicalaPrincipala"]) + @","
                    + idStareRecord + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdAccident"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdTipFinantareSectie"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CNPMama"].ToString().Trim(), true) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["NFOMama"].ToString().Trim(), true) + ")", conn);
                }

                Hashtable coduriPacienti = new Hashtable();
                DataSet ds_PacientFisa = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdPacientFisa, CazID from Pacient_Fisa", conn);
                foreach (DataRow dr in ds_PacientFisa.Tables[0].Rows)
                    coduriPacienti[dr["CazID"].ToString().Trim()] = dr["IdPacientFisa"];
                coduriPacienti["NULL"] = "NULL";
                coduriPacienti[""] = "NULL";
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Fisa", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - diagnostice (secundare)
                start = DateTime.Now;
                DataView dv_Pacient_DiagnosticeSecundare = dsSelectExportData_DRG.Tables[4].DefaultView;

                for (int i = 0; i < dv_Pacient_DiagnosticeSecundare.Count; ++i)
                {
                    string cazId = dv_Pacient_DiagnosticeSecundare[i]["CaseID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }
                    string codDiag = dv_Pacient_DiagnosticeSecundare[i]["code"].ToString().Trim();

                    //sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_DiagnosticeSecundare(IdPacientFisa, IdDiagnosticSecundar) "
                    //     + " VALUES(" + idPacient + "," + idDiag + ")", conn);
                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_DiagnosticeSecundare", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - sectii
                start = DateTime.Now;
                DataView dv_Pacient_Sectii = dsSelectExportData_DRG.Tables[5].DefaultView;

                for (int i = 0; i < dv_Pacient_Sectii.Count; ++i)
                {
                    string cazId = dv_Pacient_Sectii[i]["Caz_ID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string sectieCautata = dv_Pacient_Sectii[i]["Sectie_ID"].ToString() + "-" + dv_Pacient_Sectii[i]["NumarSectie"].ToString();
                    //object sectieInt = sectii[new System.Collections.Generic.KeyValuePair<object, object>(dv_Pacient_Sectii[i]["Sectie_ID"].ToString().Trim(), dv_Pacient_Sectii[i]["NumarSectie"].ToString().Trim())];
                    //if (sectieInt == null)
                    //{
                    //    ++totalPacientiNerepartizati;
                    //    if (!pacientiNepreluatiPeSectie.ContainsKey(sectieCautata))
                    //        pacientiNepreluatiPeSectie[sectieCautata] = -1;
                    //    continue;
                    //}

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Sectii (IdPacientFisa, IdSectie,DataInternare, DataExternare) VALUES("
                         + idPacient + ","
                         //+ sectieInt + ","
                         + Tools.getSQLFormat_ISO(dv_Pacient_Sectii[i]["DataInternare"]) + ","
                         + Tools.getSQLFormat_ISO(dv_Pacient_Sectii[i]["DataExternare"]) + ")", conn);

                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Sectii", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - proceduri
                start = DateTime.Now;
                DataView dv_Pacient_Proceduri = dsSelectExportData_DRG.Tables[6].DefaultView;

                for (int i = 0; i < dv_Pacient_Proceduri.Count; ++i)
                {
                    string cazId = dv_Pacient_Proceduri[i]["caz_ID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Proceduri(IdPacientFisa, IdProcedura, Cantitate, Invalid) VALUES("
                         + idPacient + ","
                         //+ coduriProc[dv_Pacient_Proceduri[i]["CodProcedura"].ToString().Trim()] + ","
                        //+ coduriProc[Tools.GetSQLValueNULL(dv_Pacient_Proceduri[i]["CodProcedura"].ToString().Trim(), false)] + ","
                         + Tools.GetSQLValueNULL(dv_Pacient_Proceduri[i]["Cantitate"].ToString().Trim(), false) + ", No)", conn);
                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Proceduri", conn).Tables[0].Rows[0][0];
                #endregion

                this.SetMessage(Resources.DatabaseStrings.DocumentSaved2 + " S-au importat " + (dv_Pacient_Fisa.Count - totalPacientiNepreluati) + " pacienti din " + dv_Pacient_Fisa.Count + ".", totalPacientiNepreluati > 0);
            }
            //***Spitalizare Zi***//
            else if (drpExportType.SelectedItem.Text == "Export DRG SPZ")
            {
                #region Pacient Fisa SZ
                Hashtable sectii = new Hashtable();
                DataSet ds_Lista_Sectii___Filtrat = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdSectie, CodSectie, NumarSectie from Lista_Sectii___Filtrat", conn);
                foreach (DataRow dr in ds_Lista_Sectii___Filtrat.Tables[0].Rows)
                    sectii[new System.Collections.Generic.KeyValuePair<object, object>(dr["CodSectie"].ToString().Trim(), dr["NumarSectie"].ToString().Trim())] = dr["IdSectie"];
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Lista_Sectii___Filtrat", conn).Tables[0].Rows[0][0];

                #region codificare standard, deja existenta in mdb
                Hashtable coduriDiag = new Hashtable();
                DataSet ds_Diagnostice = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdDiagnostic, CodDiagnostic from Lista_Diagnostice", conn);
                foreach (DataRow dr in ds_Diagnostice.Tables[0].Rows)
                    coduriDiag[dr["CodDiagnostic"].ToString().Trim()] = dr["IdDiagnostic"];
                coduriDiag["NULL"] = "NULL";
                coduriDiag[""] = "NULL";

                Hashtable coduriProc = new Hashtable();
                DataSet ds_Proceduri = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdProcedura, CodProcedura from Lista_Proceduri", conn);
                foreach (DataRow dr in ds_Proceduri.Tables[0].Rows)
                    coduriProc[dr["CodProcedura"].ToString().Trim()] = dr["IdProcedura"];
                coduriProc["NULL"] = "NULL";
                coduriProc[""] = "NULL";

                Hashtable codesServices = new Hashtable();
                DataSet ds_Services = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdTipServiciu, DenTipServiciu from Lista_TipServicii", conn);
                foreach (DataRow dr in ds_Services.Tables[0].Rows)
                    codesServices[dr["DenTipServiciu"].ToString().Trim()] = dr["IdTipServiciu"];
                codesServices["NULL"] = "NULL";
                codesServices[""] = "NULL";

                Hashtable codesAnalises = new Hashtable();
                DataSet ds_Analises = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdAnaliza, CodAnaliza from Lista_Analize", conn);
                foreach (DataRow dr in ds_Analises.Tables[0].Rows)
                    codesAnalises[dr["CodAnaliza"].ToString().Trim()] = dr["IdAnaliza"];
                codesAnalises["NULL"] = "NULL";
                codesAnalises[""] = "NULL";

                #endregion
                start = DateTime.Now;
                DataView dv_Pacient_Fisa = dsSelectExportData_DRG.Tables[3].DefaultView;

                for (int i = 0; i < dv_Pacient_Fisa.Count; ++i)
                {
                    string cazId = dv_Pacient_Fisa[i]["Caz_ID"].ToString().Trim();

                    string sectieCautata = dv_Pacient_Fisa[i]["Sectie_ID"].ToString() + "-" + dv_Pacient_Fisa[i]["NumarSectie"].ToString();
                    object sectieInt = sectii[new System.Collections.Generic.KeyValuePair<object, object>(dv_Pacient_Fisa[i]["Sectie_ID"].ToString().Trim(), dv_Pacient_Fisa[i]["NumarSectie"].ToString().Trim())];
                    if (sectieInt == null)
                    {
                        totalPacientiNepreluati++;
                        pacientiNepreluatiPeSectie[sectieCautata] = 1 + (int)(pacientiNepreluatiPeSectie.ContainsKey(sectieCautata) ? pacientiNepreluatiPeSectie[sectieCautata] : 0);
                        pacientiNepreluati.Add(cazId);
                        continue;
                    }

                    if (dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod1"] == System.DBNull.Value)
                    {
                        totalPacientiNepreluati++;
                        pacientiNepreluati.Add(cazId);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Fisa___SZ
				    (CazID,IdSpital,IdSectie,NFO,DataDeschidereFisa,DataInchidereFisa,CNP,Nume,Prenume,IdLocalitate,Strada,NrStrada,DataNastere,Sex,
				    IdNivelInstruire,IdOcupatie,IdTipCetatenie,IdCetatenie,IdStatutAsigurat,IdTipAsigurareCNAS,IdCAS,IdCategorieAsiguratCNAS,IdMedic,
				    IdDP1,IdDP2,IdAccident,IdStareRecord,IdTipFinantare,SelectieExport,
				    Finalizat,IdUtilizator,DataOperare) VALUES('" + cazId + @"',"
                    + idSpital + @","
                    + sectieInt + @","
                    + dv_Pacient_Fisa[i]["NumarFoaieObservatie"].ToString().Trim() + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataDeschidereFisa"]) + @","
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataInchidereFisa"]) + @","
                     + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CNP"].ToString().Trim(), true) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Nume"].ToString().Trim(), true) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["Prenume"].ToString().Trim(), true) + @","
                    + dv_Pacient_Fisa[i]["IdLocalitate"].ToString().Trim() + @","
                    + "NULL,"
                    + "NULL,"
                    + Tools.getSQLFormat_ISO(dv_Pacient_Fisa[i]["DataNastere"]) + @","
                    + GetGender(dv_Pacient_Fisa[i]["sex"].ToString()) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdNivelInstruire"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdOcupatie"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["IdTipCetatenie"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCetatenie"].ToString().Trim(), false) + @","
                    + dv_Pacient_Fisa[i]["IdStatutAsigurat"].ToString().Trim() + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDTipAsigurareCNAS"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IDCAS"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdCategorieAsiguratCNAS"].ToString().Trim(), false) + @","
                    //+ coduriMedic[dv_Pacient_Fisa[i]["CodMedic"].ToString().Trim()] + @","
                    + coduriDiag[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod1"].ToString().Trim(), false)] + @","
                    + coduriDiag[Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["CodDiagnosticPrincipalCod2"].ToString().Trim(), false)] + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdAccident"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim() == "0" ? "1" : dv_Pacient_Fisa[i]["IdStareRecord"].ToString().Trim(), false) + @","
                    + Tools.GetSQLValueNULL(dv_Pacient_Fisa[i]["IdTipFinantare"].ToString().Trim(), false) + @","
                    + ConvertBoolToYesNo(dv_Pacient_Fisa[i]["Flag_SelectieExport"]) + @","
                    + ConvertBoolToYesNo(dv_Pacient_Fisa[i]["Flag_Finalizat"]) + @","
                    + "1,"
                    + "NULL)", conn);
                }

                Hashtable coduriPacienti = new Hashtable();
                DataSet ds_PacientFisa = Hipocrate.DB.DBConnection.GetDataSet_Access("select IdPacientFisa, CazID from Pacient_Fisa___SZ", conn);
                foreach (DataRow dr in ds_PacientFisa.Tables[0].Rows)
                    coduriPacienti[dr["CazID"].ToString().Trim()] = dr["IdPacientFisa"];
                coduriPacienti["NULL"] = "NULL";
                coduriPacienti[""] = "NULL";
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Fisa___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - diagnostice (secundare) SZ
                start = DateTime.Now;
                DataView dv_Pacient_DiagnosticeSecundare = dsSelectExportData_DRG.Tables[4].DefaultView;

                for (int i = 0; i < dv_Pacient_DiagnosticeSecundare.Count; ++i)
                {
                    string cazId = dv_Pacient_DiagnosticeSecundare[i]["CaseID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string codDiag = dv_Pacient_DiagnosticeSecundare[i]["code"].ToString().Trim();
                    object idDiag = coduriDiag[Tools.GetSQLValueNULL(codDiag, false)];
                    if (idDiag == null)
                    {
                        ++totalDiagnosticeNegasite;
                        diagnosticeNegasite[codDiag] = 1 + (int)(diagnosticeNegasite.ContainsKey(codDiag) ? diagnosticeNegasite[codDiag] : 0);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_DiagnosticeSecundare___SZ(IdPacientFisa, IdDiagnosticSecundar) "
                         + " VALUES(" + idPacient + "," + idDiag + ")", conn);
                }
                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_DiagnosticeSecundare___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - proceduri
                start = DateTime.Now;
                DataView dv_Pacient_Procedures = dsSelectExportData_DRG.Tables[5].DefaultView;
                DataTable pacientVisit_Table = dv_Pacient_Procedures.ToTable("Pacient_Visit", true, "caz_id", "ProcDate");

                for (int i = 0; i < pacientVisit_Table.Rows.Count; ++i)
                {
                    string cazId = pacientVisit_Table.Rows[i]["caz_ID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Vizite___SZ(IdPacientFisa, DataVizita) "
                         + " VALUES(" + idPacient + ","
                         + Tools.getSQLFormat_ISO(pacientVisit_Table.Rows[i]["ProcDate"]) + ")", conn);
                }

                for (int i = 0; i < dv_Pacient_Procedures.Count; ++i)
                {
                    string strQueryAccess = "select [Pacient_Vizite___SZ].IdPacientVizita from Pacient_Fisa___SZ INNER JOIN [Pacient_Vizite___SZ] ON [Pacient_Vizite___SZ].IdPacientFisa = Pacient_Fisa___SZ.IdPacientFisa where Pacient_Fisa___SZ.CazId = '" + dv_Pacient_Procedures[i]["caz_id"].ToString().Trim() + "' AND [Pacient_Vizite___SZ].DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Procedures[i]["ProcDate"])) + "#";
                    DataSet dsidPacientVizita = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);
                    object idPacientVizita = null;

                    if (dsidPacientVizita.Tables[0].Rows.Count > 0)
                        idPacientVizita = dsidPacientVizita.Tables[0].Rows[0][0];
                    else if (idPacientVizita == null)
                    {
                        proceduriNepreluate.Add(dv_Pacient_Procedures[i]["caz_id"].ToString());
                        continue;
                    }

                    string codProc = dv_Pacient_Procedures[i]["codprocedura"].ToString().Trim();
                    object idProc = coduriProc[Tools.GetSQLValueNULL(codProc, false)];
                    if (idProc == null)
                    {
                        proceduriNepreluate.Add(dv_Pacient_Procedures[i]["caz_id"].ToString());
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Proceduri___SZ(IdPacientVizita, IdProcedura,Cantitate) "
                         + " VALUES(" + idPacientVizita + ","
                         + coduriProc[Tools.GetSQLValueNULL(dv_Pacient_Procedures[i]["CodProcedura"].ToString().Trim(), false)] + @","
                         + Tools.GetSQLValueNULL(dv_Pacient_Procedures[i]["Cantitate"].ToString().Trim(), false) + ")", conn);
                }

                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_Proceduri___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - servicii
                start = DateTime.Now;
                DataView dv_Pacient_Services = dsSelectExportData_DRG.Tables[6].DefaultView;
                DataTable pacientVisit_TableService = dv_Pacient_Services.ToTable("Pacient_Visit", true, "caseid", "ServiceDate");

                for (int i = 0; i < pacientVisit_TableService.Rows.Count; ++i)
                {
                    string cazId = pacientVisit_TableService.Rows[i]["caseID"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string strQueryAccess = "select IdPacientFisa, DataVizita from Pacient_Vizite___SZ where IdPacientFisa=" + idPacient + " AND DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Services[i]["ServiceDate"])) + "#";
                    DataSet dsverifyData = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);

                    if (dsverifyData.Tables[0].Rows.Count == 0)
                    {
                        sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Vizite___SZ(IdPacientFisa, DataVizita) "
                             + " VALUES(" + idPacient + ","
                             + Tools.getSQLFormat_ISO(pacientVisit_TableService.Rows[i]["ServiceDate"]) + ")", conn);
                    }
                    else
                        continue;
                }

                for (int i = 0; i < dv_Pacient_Services.Count; ++i)
                {
                    string strQueryAccess = "select [Pacient_Vizite___SZ].IdPacientVizita from Pacient_Fisa___SZ INNER JOIN [Pacient_Vizite___SZ] ON [Pacient_Vizite___SZ].IdPacientFisa = Pacient_Fisa___SZ.IdPacientFisa where Pacient_Fisa___SZ.CazId = '" + dv_Pacient_Services[i]["caseID"].ToString().Trim() + "' AND [Pacient_Vizite___SZ].DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Services[i]["ServiceDate"])) + "#";
                    DataSet dsidPacientVizita = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);
                    object idPacientVizita = null;
                    if (dsidPacientVizita.Tables[0].Rows.Count > 0)
                        idPacientVizita = dsidPacientVizita.Tables[0].Rows[0][0];
                    else if (idPacientVizita == null)
                        continue;

                    if (dv_Pacient_Services[i]["code"] == System.DBNull.Value)
                    {
                        serviciiNepreluate.Add(dv_Pacient_Services[i]["caseid"].ToString());
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_TipServicii___SZ(IdPacientVizita, IdTipServiciu) "
                         + " VALUES(" + idPacientVizita + ","
                         + codesServices[Tools.GetSQLValueNULL(dv_Pacient_Services[i]["Code"].ToString().Trim(), false)] + ")", conn);
                }

                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_TipServicii___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                #region pacient - analize
                start = DateTime.Now;
                DataView dv_Pacient_Analises = dsSelectExportData_DRG.Tables[7].DefaultView;
                DataTable pacientVisit_TableAnalise = dv_Pacient_Analises.ToTable("Pacient_Visit", true, "caz_id", "AnalyseDate");

                for (int i = 0; i < pacientVisit_TableAnalise.Rows.Count; ++i)
                {
                    string cazId = pacientVisit_TableAnalise.Rows[i]["caz_id"].ToString().Trim();
                    object idPacient = coduriPacienti[Tools.GetSQLValueNULL(cazId, false)];
                    if (idPacient == null)
                    {
                        if (!pacientiNepreluati.Contains(cazId))
                            if (!pacientiNegasiti.Contains(cazId))
                                pacientiNegasiti.Add(cazId);
                        continue;
                    }

                    string strQueryAccess = "select IdPacientFisa, DataVizita from Pacient_Vizite___SZ where IdPacientFisa=" + idPacient + " AND DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(pacientVisit_TableAnalise.Rows[i]["AnalyseDate"])) + "#";
                    DataSet dsverifyData = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);

                    if (dsverifyData.Tables[0].Rows.Count == 0)
                    {
                        sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_Vizite___SZ(IdPacientFisa, DataVizita) "
                        + " VALUES(" + idPacient + ","
                        + Tools.getSQLFormat_ISO(pacientVisit_TableAnalise.Rows[i]["AnalyseDate"]) + ")", conn);
                    }
                    else
                        continue;
                }

                for (int i = 0; i < dv_Pacient_Analises.Count; ++i)
                {
                    string strQueryAccess = "select [Pacient_Vizite___SZ].IdPacientVizita from Pacient_Fisa___SZ INNER JOIN [Pacient_Vizite___SZ] ON [Pacient_Vizite___SZ].IdPacientFisa = Pacient_Fisa___SZ.IdPacientFisa where Pacient_Fisa___SZ.CazId = '" + dv_Pacient_Analises[i]["caz_id"].ToString().Trim() + "' AND [Pacient_Vizite___SZ].DataVizita = #" + Tools.getSQLFormat_120(Convert.ToDateTime(dv_Pacient_Analises[i]["AnalyseDate"])) + "#";
                    DataSet dsidPacientVizita = Hipocrate.DB.DBConnection.GetDataSet_Access(strQueryAccess, conn);
                    object idPacientVizita = null;
                    if (dsidPacientVizita.Tables[0].Rows.Count > 0)
                        idPacientVizita = dsidPacientVizita.Tables[0].Rows[0][0];
                    else if (idPacientVizita == null)
                        continue;

                    if (dv_Pacient_Analises.Table.Rows[i]["CodAnaliza"] == System.DBNull.Value)
                    {
                        analizeNepreluate.Add(dv_Pacient_Analises[i]["caz_id"].ToString());
                        continue;
                    }

                    object idAnaliza = codesAnalises[Tools.GetSQLValueNULL(dv_Pacient_Analises[i]["CodAnaliza"].ToString().Trim(), false)];
                    if (idAnaliza == null)
                    {
                        analizeNepreluate.Add(dv_Pacient_Analises[i]["caz_id"].ToString());
                        continue;
                    }

                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(@"insert into Pacient_AnalizeLaborator___SZ(IdPacientVizita, IdAnaliza,Cantitate) "
                         + " VALUES(" + idPacientVizita + ","
                         + codesAnalises[Tools.GetSQLValueNULL(dv_Pacient_Analises[i]["CodAnaliza"].ToString().Trim(), false)] + @","
                         + Tools.GetSQLValueNULL(dv_Pacient_Analises[i]["Cantitate"].ToString().Trim(), false) + ")", conn);
                }

                length[currentTable] = DateTime.Now.Subtract(start);
                size[currentTable++] = Hipocrate.DB.DBConnection.GetDataSet_Access(@"select count(*) from Pacient_AnalizeLaborator___SZ", conn).Tables[0].Rows[0][0];
                #endregion

                this.SetMessage(Resources.DatabaseStrings.DocumentSaved2 + " S-au importat " + (dv_Pacient_Fisa.Count - totalPacientiNepreluati) + " pacienti din " + dv_Pacient_Fisa.Count + ".", totalPacientiNepreluati > 0);
            }

            #region detalii erori
            output.Text += "Export terminat. <font color=\"red\">&nbsp;";
            if (totalPacientiNepreluati > 0)
            {
                output.Text += "<br/><br/>Numar pacienti nepreluati: " + totalPacientiNepreluati;
                foreach (string sectieNegasita in pacientiNepreluatiPeSectie.Keys)
                    if (((int)pacientiNepreluatiPeSectie[sectieNegasita]) > 0)
                        output.Text += string.Format("<br/>Sectia '{0}' : {1}", sectieNegasita, pacientiNepreluatiPeSectie[sectieNegasita]);
            }
            if (proceduriNepreluate.Count > 0)
            {
                output.Text += "<br/><br/>Proceduri care nu au corespondent cu pacientii: " + proceduriNepreluate.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in proceduriNepreluate)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (serviciiNepreluate.Count > 0)
            {
                output.Text += "<br/><br/>Servicii care nu au fost preluate pe pacientii: " + serviciiNepreluate.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in serviciiNepreluate)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (analizeNepreluate.Count > 0)
            {
                output.Text += "<br/><br/>Analize care nu au fost preluate pe pacientii: " + analizeNepreluate.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in analizeNepreluate)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (pacientiNegasiti.Count > 0)
            {
                output.Text += "<br/><br/>Pacienti care nu au fost gasiti : " + pacientiNegasiti.Count + "<div style='overflow: scroll; max-height: 250px; vertical-align: top; width: 50%'>";
                foreach (string cazId in pacientiNegasiti)
                    output.Text += "<br/>" + cazId;
                output.Text += "</div>";
            }
            if (totalPacientiNerepartizati > 0)
            {
                output.Text += "<br/><br/>Pacienti nerepartizati pe sectie (in Pacient_Sectii), din cauza sectiei gresite: " + totalPacientiNerepartizati;
                foreach (string sectieGresita in pacientiNepreluatiPeSectie.Keys)
                    if (((int)pacientiNepreluatiPeSectie[sectieGresita]) < 0)
                        output.Text += string.Format("<br/>Sectia '{0}' : {1}", sectieGresita, pacientiNepreluatiPeSectie[sectieGresita]);
            }
            if (totalDiagnosticeNegasite > 0)
            {
                output.Text += "<br/><br/>Diagnostice care nu au fost gasite in tabela standard : " + totalDiagnosticeNegasite;
                foreach (string codDiag in diagnosticeNegasite.Keys)
                    output.Text += string.Format("<br/>Cod Diagnostic '{0}' : {1}", codDiag, diagnosticeNegasite[codDiag]);
            }
            output.Text += "&nbsp;</font>";
            #endregion
        }
        catch (Exception ex)
        {
            //this.SetMessage(ex.Message, true);
            if (output.Text.Length > 0)
                output.Text += "<br /><br />";
            output.Text += "<strong>Exceptie</strong>: " + ex.ToString();
        }
        finally
        {
            if (conn != null)
                conn.Close();
            string js = "hideModal()";
            ScriptManager.RegisterStartupScript(this, typeof(ExportDRG), "hideModal", js, true);
            if (startAll != DateTime.MinValue)
            {
                output.Text += "<br/><br/><table><tr><th>Durata de executie:</th><th>" + TimeSpanToString(DateTime.Now.Subtract(startAll)) + "</th><th>&nbsp;</th></tr>";
                for (int i = 0; i < tables.Length; ++i)
                    output.Text += string.Format("<tr><td>{0}</td><td>{1}</td><td align=\"right\">{2}</td></tr>", tables[i], TimeSpanToString(length[i]), size[i]);
                output.Text += "</table>";
                tblTitlu.Style.Add("width", "99.3%");
            }
        }
    }

    #region misc functions
    private string TimeSpanToString(TimeSpan ts)
    {
        //the default ts.ToString() returns "HH:mm:ss.xxxxxxx"...
        return ts.Minutes + "'" + ts.Seconds.ToString("00") + @"""";
    }

    private string ConvertBoolToYesNo(object sqlValue)
    {
        try
        {
            if (string.IsNullOrEmpty(sqlValue.ToString())) 
                return "False";

            switch (sqlValue.ToString().ToLower())
            {
                case "0":
                case "false":
                    return "No";
                case "1":
                case "true":
                    return "Yes";
            }

            return (System.Convert.ToInt32(sqlValue.ToString()) == 1 ? "True" : "False");
        }
        catch (FormatException)
        {
            throw new ApplicationException("Cannot conv 2 bool: " + sqlValue.ToString());
        }
    }

    private string GetGender(string boolValue)
    {
        if (boolValue.Equals("True"))
            return "Yes";
        else if(boolValue.Equals("False"))
            return "No";
        else
            return  "No"; 
    }
    #endregion

    protected void btnClear_Click(object sender, EventArgs e)
    {
        output.Text = "";
        try
        {
            string path = Server.MapPath("~/Tools/TempFiles/DRGNational2008.mdb");
            string pathIni = Server.MapPath("~/Tools/TempFiles/DRGNational2008_ini.mdb");
            string mesaj = "";

            if (System.IO.File.Exists(pathIni))
            {
                try
                {
                    System.IO.File.Copy(pathIni, path, true);
                    output.Text = "Fisierul a fost suprascris direct.";
                    return;
                }
                catch (System.IO.IOException) { }
            }

            string[] tables = new string[] { "Lista_Spitale___Filtrat", "Lista_Medici", "Lista_Sectii___Filtrat",
				"Pacient_Fisa", "Pacient_DiagnosticeSecundare", "Pacient_Sectii", "Pacient_Proceduri" };
            string[] fieldNames = new string[] { "@IdSpital", "@IdMedic", "@IdSectie", "@IdPacientFisa", //null, null, null, null, //
				"IdPacientDiagnosticSecundar", "IdPacientSectie", "IdPacientProcedura" };

            for (int i = tables.Length; i-- > 0; )
                CleanTable(tables[i], fieldNames[i], path, ref mesaj);

            this.SetMessage(Resources.DatabaseStrings.DocumentSaved2, false);
            if (mesaj.Length > 0)
                output.Text = "Tabele pt care nu a functionat reset-ul:<br/>" + mesaj;
            else
                output.Text = "Tabelele au fost golite si resetate.";
        }
        catch (Exception ex)
        {
            //this.SetMessage(ex.Message, true);
            output.Text = ex.ToString();
        }
    }

    private void CleanTable(string tableName, string fieldName_ID, string path, ref string outputMessage)
    {
        object sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access("delete from " + tableName, path);
        try
        {
            if (string.IsNullOrEmpty(fieldName_ID))
                return;
            if (!fieldName_ID.StartsWith("@"))
                sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access("ALTER TABLE " + tableName + " ALTER COLUMN " + fieldName_ID + " Counter(1,1)", path);
            else
            {
                string[] recreate = new string[] { };
                switch (fieldName_ID)
                {
                    case "@IdSpital": //pacient fisa, lista sectii filtrat
                        sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access("ALTER TABLE Pacient_Fisa drop CONSTRAINT [FK_Pacient_Fisa_Lista_Spitale___Filtrat]", path);
                        sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access("ALTER TABLE Lista_Sectii___Filtrat drop CONSTRAINT [FK_Lista_Sectii___Filtrat_Lista_Spitale___Filtrat]", path);
                        recreate = new string[]{ @"ALTER TABLE Pacient_Fisa WITH CHECK ADD CONSTRAINT [FK_Pacient_Fisa_Lista_Spitale___Filtrat]
							FOREIGN KEY([IdSpital]) REFERENCES Lista_Spitale___Filtrat ([IdSpital])",
								@"ALTER TABLE Lista_Sectii___Filtrat WITH CHECK ADD CONSTRAINT [FK_Lista_Sectii___Filtrat_Lista_Spitale___Filtrat]
							FOREIGN KEY([IdSpital]) REFERENCES Lista_Spitale___Filtrat ([IdSpital])" };
                        break;
                    case "@IdMedic": //etc
                        break;
                    case "@IdSectie":
                        break;
                    case "@IdPacientFisa":
                        break;
                    default:
                        throw new ApplicationException("Unknown ID field: " + fieldName_ID);
                }
                sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access("ALTER TABLE " + tableName + " ALTER COLUMN " + fieldName_ID.Substring(1) + " Counter(1,1)", path);
                foreach (string constraint in recreate)
                    sqlReturnValue = Hipocrate.DB.DBConnection.ExecuteScalarQuery_Access(constraint, path);
            }
        }
        catch (ApplicationException ex)
        {
            if ((ex.InnerException == null) || (ex.InnerException.InnerException == null))
                throw;
            Exception e = ex.InnerException.InnerException;
            if (e is OleDbException)
                outputMessage += e.Message + "<br/>";
            else
                throw;
        }
    }

    protected void btnThread_Click(object sender, EventArgs e)
    {
        try
        {
            string guid = System.Guid.NewGuid().ToString("N");

            //*
            Thread newThread = new Thread(Test);
            newThread.Priority = ThreadPriority.Normal;
            newThread.Start(newThread);

            ViewState["threadCount"] = 1 + (int)(ViewState["threadCount"] ?? 0);
            //*/
            mesajStatus.Text = "Initializare"; // + ViewState["threadCount"];

            //ThreadManager.SetServerReference(this.Server);
            //ThreadManager.StartThread();
            //mesajStatus.Text += ThreadManager.Message;
        }
        catch (Exception ex)
        {
            output.Text = ex.ToString();
        }
    }

    protected void btnDownload_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/Tools/ExportMDB.ashx");
    }

    protected void btnThreadStatus_Click(object sender, EventArgs e)
    { 
        
    }

    public class ThreadManager
    {
        private ThreadManager() { }

        private static ThreadManager singletonInstance = new ThreadManager();

        //public static ThreadManager Instance { get { return singletonInstance; } }

        private static HttpServerUtility serverRef = null;
        public static void SetServerReference(HttpServerUtility callingContext)
        {
            serverRef = callingContext;
        }

        private static List<Thread> activeThreads = new List<Thread>();
        private static List<Thread> oldThreads = new List<Thread>();

        public static void StartThread()
        {
            Log("starting new thread");
            Thread newThread = new Thread(Test);
            newThread.Priority = ThreadPriority.Normal;
            activeThreads.Add(newThread);
            newThread.Start(newThread);
            Log("sent 'start' to the new thread...");
        }

        public static int ThreadCount
        {
            get
            {
                Log("ThreadCount requested");
                return activeThreads.Count;
            }
        }

        private static string message = ".";
        public static string Message
        {
            get
            {
                Log("Message requested");
                List<Thread> notYetFinished = new List<Thread>();
                foreach (Thread t in oldThreads)
                    if ((t.ThreadState != ThreadState.Aborted) && (t.ThreadState != ThreadState.Stopped))
                        notYetFinished.Add(t);
                oldThreads.Clear();
                oldThreads.AddRange(notYetFinished);
                string reply = string.Format("{0} ({1} a.t., {2} stalled)", message, ThreadCount, notYetFinished.Count);
                Log("Sent reply: " + reply);
                return reply;
            }
        }

        private static void Test(object myself)
        {
            Log("Testing...");
            for (int i = 0; i < 5; i++)
            {
                Log("Sleeping. i = " + i);
                System.Threading.Thread.Sleep(5000);
                Log("Slept for i = " + i + ", setting message.");
                message = "i = " + i.ToString();
            }
            Log("Test completed.");
            activeThreads.Remove(myself as Thread);
            oldThreads.Add(myself as Thread);
            //throw new ApplicationException("Test finished after 25 seconds...");
            //(myself as Thread).Abort();
        }

        private static List<System.Collections.Generic.KeyValuePair<DateTime, string>> log = null;
        private static void Log(string message)
        {
            System.Diagnostics.Trace.TraceInformation("TM Log: " + message);
            if (log == null) log = new List<KeyValuePair<DateTime, string>>();
            log.Add(new KeyValuePair<DateTime, string>(DateTime.Now, message));
            if ((log.Count % 10) == 0)
            {
                message += "*";
                Tools.ScrieInLog(serverRef, LogContents);
            }
        }
        public static int LogSize { get { return log.Count; } }
        public static string LogContents
        {
            get
            {
                System.Text.StringBuilder dumper = new System.Text.StringBuilder();
                dumper.AppendLine("*** Thread Manager Log: ***");
                foreach (KeyValuePair<DateTime, string> line in log)
                {
                    dumper.Append(line.Key);
                    dumper.Append(" : ");
                    dumper.AppendLine(line.Value);
                }
                dumper.AppendLine("*** END of Thread Manager Log ***");
                return dumper.ToString();
            }
        }
    }

    public void Test(object myself)
    {
        string js = "";
        for (int i = 0; i < 3; i++)
        {
            mesajStatusAjax.Text = "sleeping. i = " + i;
            upStatus.Update();
            System.Diagnostics.Trace.TraceInformation(mesajStatusAjax.Text);
            System.Threading.Thread.Sleep(4000);
            statusExport = "i = " + i.ToString();
            mesajStatus.Text = statusExport;
            js = "testPage(" + i.ToString() + ")";
            //ScriptManager.RegisterStartupScript(this, typeof(ExportDRG), "test" + i.ToString(), js, true);
        }
        ViewState["threadCount"] = (int)(ViewState["threadCount"] ?? -665) - 1;
        throw new ApplicationException("Test finished after 25 seconds...");
        //(myself as Thread).Abort();
    }

    private static string statusExport = "";

    [WebMethod]
    public static string getStatus()
    {
        return DateTime.Now.ToString() + " : " + statusExport;
    }

    protected void tabActive_TabChanged(object sender, EventArgs e)
    {
        if (tabContainer.ActiveTabIndex == 1)
        {
            LoadCombo(null,null);
            LoadDataGrid();
        }

        if (tabContainer.ActiveTabIndex == 2)
        {
            LoadComboVerify(null, null);
            LoadDataGridVerify();
        }

    }
}
